none
Add data to power query RRS feed

  • Question

  • Hi, 

    I opened a folder to which I am saving excel data located at this address:

    http://www.lme.com/metals/reports/averages/

    My Q is how to create a query to import all the files located in the folder in this way:

    Each Excel saved in the folder has the same formation:

    The query needs to pull the data of the first 11 tabs (from the left, till ABR tab), manipulate the data to contain a table with these columns:

    "Index" column= data in cell B4, repeats itself

    "Date" column= which contains the data in column B

    "Cash Buyer" column= which contains the data in column C

    *No need for the data: Avg, high, low

    Appreciate any help!


    • Edited by עמיתת Friday, May 1, 2015 7:22 PM
    Friday, May 1, 2015 7:18 PM

Answers

  • Hi,

    this article describes how to import multiple files from a folder:

    http://blogs.adatis.co.uk/blogs/dan/archive/2014/09/25/importing-multiple-files-into-power-query.aspx

    Here's some code on how to do some simple cleanup on this data:

    let
        Source = Folder.Files("YOUR_FOLDER"),
        AddCust = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content])),
        Expand = Table.ExpandTableColumn(AddCust, "Custom", {"Name", "Data"}, {"Name.1", "Data"}),
        ExpandData = Table.ExpandTableColumn(Expand, "Data", {"Column2", "Column3"}, {"Column2", "Column3"}),
        RemoveOtherCols = Table.SelectColumns(ExpandData,{"Name.1", "Column2", "Column3"}),
        Rename = Table.RenameColumns(RemoveOtherCols,{{"Column2", "Date"}, {"Column3", "Value"}}),
        ChangeType = Table.TransformColumnTypes(Rename,{{"Date", type date}}),
        RemoveErrors = Table.RemoveRowsWithErrors(ChangeType, {"Date"}),
        ChgType = Table.TransformColumnTypes(RemoveErrors,{{"Value", type number}}),
        RemError = Table.RemoveRowsWithErrors(ChgType, {"Value"}),
        Filter = Table.SelectRows(RemError, each ([Value] <> null) and ([Name.1] <> "ABR" and [Name.1] <> "ABR Avg" and [Name.1] <> "Averages Inc. Euro Eq")),
        Sort = Table.Sort(Filter,{{"Name.1", Order.Ascending}, {"Date", Order.Ascending}})
    in
        Sort

    In order to fetch the index field in B4 of each sheet, you'd need to invoke a function (but maybe the sheetnames that will be present in the solution above will be enough):

    http://datapigtechnologies.com/blog/index.php/using-power-query-to-combine-data-from-multiple-excel-files-into-one-table/


    Imke

    Monday, May 4, 2015 2:44 PM
    Moderator

All replies

  • Appreciate help in this topic :)

    עמית

    Monday, May 4, 2015 11:00 AM
  • Hi,

    this article describes how to import multiple files from a folder:

    http://blogs.adatis.co.uk/blogs/dan/archive/2014/09/25/importing-multiple-files-into-power-query.aspx

    Here's some code on how to do some simple cleanup on this data:

    let
        Source = Folder.Files("YOUR_FOLDER"),
        AddCust = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content])),
        Expand = Table.ExpandTableColumn(AddCust, "Custom", {"Name", "Data"}, {"Name.1", "Data"}),
        ExpandData = Table.ExpandTableColumn(Expand, "Data", {"Column2", "Column3"}, {"Column2", "Column3"}),
        RemoveOtherCols = Table.SelectColumns(ExpandData,{"Name.1", "Column2", "Column3"}),
        Rename = Table.RenameColumns(RemoveOtherCols,{{"Column2", "Date"}, {"Column3", "Value"}}),
        ChangeType = Table.TransformColumnTypes(Rename,{{"Date", type date}}),
        RemoveErrors = Table.RemoveRowsWithErrors(ChangeType, {"Date"}),
        ChgType = Table.TransformColumnTypes(RemoveErrors,{{"Value", type number}}),
        RemError = Table.RemoveRowsWithErrors(ChgType, {"Value"}),
        Filter = Table.SelectRows(RemError, each ([Value] <> null) and ([Name.1] <> "ABR" and [Name.1] <> "ABR Avg" and [Name.1] <> "Averages Inc. Euro Eq")),
        Sort = Table.Sort(Filter,{{"Name.1", Order.Ascending}, {"Date", Order.Ascending}})
    in
        Sort

    In order to fetch the index field in B4 of each sheet, you'd need to invoke a function (but maybe the sheetnames that will be present in the solution above will be enough):

    http://datapigtechnologies.com/blog/index.php/using-power-query-to-combine-data-from-multiple-excel-files-into-one-table/


    Imke

    Monday, May 4, 2015 2:44 PM
    Moderator
  • Brilliant!

    Thx a lot! 


    עמית

    Tuesday, May 5, 2015 12:27 PM