Forum Discussion

amikm's avatar
amikm
Helper V
4 years ago

Combine multiple files in Power query editor

I am trying to combine the multiple files present in a folder C:\v-amkm\PowerBI   The data is present is each file like this: (file location https://drive.google.com/drive/folders/1IbpAobUzsm...
Need Help
amikm's avatar
amikm
Helper V
4 years ago

Thanks, sevenhills, your solution is working 100 % exactly, how I was expecting. But, later I realized the files uploaded by the business is not a CSV file, it's a .xls file, and when I tried to modify the logic as per excel, it is not working as expected, I am sure there is some link which I am missing as I am not that much good at M-query 😞

But, still, I am trying. I use the below the code change

 

 

let
    Source = (Parameter1) => let
        Source = Excel.Workbook(File.Contents("C:\Users\v-amkm\P1.xls"), null, true),
        #"Added Index" = Table.AddIndexColumn(Source, "File Index", 1, 1, Int64.Type)
    in
        #"Added Index"
in
    Source

 

I have uploaded the files to the same location (https://drive.google.com/drive/folders/1IbpAobUzsmHSwldGibVzfDUqHEglo_3z?usp=sharing)

Looking back to hear from your side 


Thanks,

 

sevenhills's avatar
sevenhills
Super User
4 years ago

In a nutshell, the logic is same and all the M Queries posted above need to be adjusted to fit for Excel. Not that much effort in my view.

 

For Transform file M query

 

let
    Source = (Parameter2) => let
        Source = Excel.Workbook(Parameter2, null, true),
        P1_Sheet = Source{[Item="P1",Kind="Sheet"]}[Data],
        #"Added Index" = Table.AddIndexColumn(P1_Sheet, "File Index", 1, 1, Int64.Type)
    in
        #"Added Index" 
in
    Source

 

Note: P1 is the sheet name I used based on CSV file conversion to .xlsx. I have to name the same sheet name for the rest of files.

 

For excel, you need to select the sheet (or) table (or) ... and the names has to be same in each excel file. I cannot download the excel file from the link, can you give public access to the excel file ... 

 

 

For (b) 

 

    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Name", "Transform File (2)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"File Index", Int64.Type}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Column1] = null then null else if Text.Contains([Column1], "Pid") then "Table 1 Row 1" else if Text.Contains([Column1], "Order placed") then "Table 2 begin" else null, type nullable text),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Conditional Column", "Custom", "Custom - Copy"),
    #"Sorted Rows" = Table.Sort(#"Duplicated Column",{ {"Name", Order.Ascending}, {"File Index", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Custom"}),
    #"Filtered Rows3" = Table.SelectRows(#"Filled Down", each ([Custom] = "Table 1 Row 1")),
    #"Trimmed Text" = Table.TransformColumns(#"Filtered Rows3",{{"Column1", Text.Trim, type text}, {"Column2", Text.Trim, type text}, {"Column3", Text.Trim, type text}}),
    #"Filtered Rows4" = Table.SelectRows(#"Trimmed Text", each ([Column1] <> null and [Column1] <> "") and ([#"Custom - Copy"] = null)),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows4",{{"Column1", "Pid"}, {"Column2", "Pname"}, {"Column3", "Amt"}, {"Name", "Source"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Source", "Pid", "Pname", "Amt"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"Pid", "Pname", "Amt", "Source"})
in
    #"Reordered Columns"

 

 

Hope this helps!