Combine multiple files in Power query editor

[amikm]

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/1IbpAobUzsmHSwldGibVzfDUqHEglo_3z?usp=sharing )-
P1.csv

----------

vendor ABC
Shipdate 01/01/2022

Pid, Pname,Amt
1,Bike,$10
2,Car,$20
3,Cycle,$40

Order placed

Custname,Pincode
101,080
102,090

P2.csv

-------------

vendor CDE
Shipdate 01/02/2022

Pid, Pname,Amt
1,Mobile,$100
2,PC,$29
3,Headphone,$5

Order placed

Custname,Pincode
103,040
104,022

P3.csv

-----------

vendor FHG
Shipdate 02/03/2022

Pid, Pname,Amt
1,laptop,$500
2,Monitor,$190
3,Mouse,$2

Order placed

Custname,Pincode
104,011
105,021

I want to grab the data from each file into a single file like this-
Final.csv

-----------
Pid, Pname,Amt,Source
1,Bike,$10,P1.csv
2,Car,$20,P1.csv
3,Cycle,$40,P1.csv

1,Mobile,$100,P2.csv
2,PC,$29,P2.csv
3,Headphone,$5,P2.csv

1,laptop,$500,P3.csv
2,Monitor,$190,P3.csv
3,Mouse,$2,P3.csv

If you have noticed, from each file I don't want to load the first 2 lines of code and any line which is written after the order placed.

When I have used Power query, I am able to see data like this-

Also, the above solution should work, If I will upload any more files to my Folder say P4.csv, P5.csv etc.

Any help on this or sample pbix will help much.

[sevenhills]

Based on your screenshot,

a) Change the "Transform file" query M code, as below code (Power Query Window > Home tab > Advanced Editor)

let
    Source = (Parameter1) => let
        Source = Csv.Document(Parameter1,[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
        #"Added Index" = Table.AddIndexColumn(Source, "File Index", 1, 1, Int64.Type)
    in
        #"Added Index"
in
    Source

b) Change the "PowerBI" query M code , ... in the advanced editor

  delete all lines from this line #"Invoke Custom Function1" till the end and copy the below code. This will give the lines you want.

FYI, I also removed empty data lines , as it wont be of any use

    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files2", "Transform File", each #"Transform File"([Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Name", "Transform File"}),
    #"Expanded Transform File" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", {"Column1", "Column2", "Column3", "File Index"}, {"Column1", "Column2", "Column3", "File Index"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Transform File",{{"File Index", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Column1], "Pid") then "Table 1 Row 1" else if Text.Contains([Column1], "Order placed") then "Table 2 begin" else null),
    #"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"

[amikm]

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]

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!  

[littlemojopuppy]

Hi @amikm 

This should help.

[amikm]

I am able to connect to the folder and grab all the files, my problem is to clean the files and grab only necessary rows from each file, and combine them in a single file. Not sure, if we need to write a function for same in M-query. But I am trying to do some data cleaning before combining all the files.

[littlemojopuppy]

@amikm you should be able to execute those steps in the Power Query editor.  Each step will be applied to all the data.  You shouldn't have any problems

[amikm]

The steps defined in that article do not solve my issue, it is straightforward to get data from the folder, but they are not talking about how to clean this up and take a header from the only first file .

1. Select Data > Get Data > From File > From Folder. The Browse dialog box appears.

2. Locate the folder containing the files you want to combine.

3. A list of the files in the folder appears in the <Folder path> dialog box. Verify that all the files you want are listed.
   
   

4. Select one of the commands at the bottom of the dialog box, for example  Combine > Combine & Load. There are additional commands discussed in the section About all those commands.

5. If you select any Combine command, The Combine Files dialog box appears. To change file settings, select each file from the Sample File box, set the File Origin, Delimiter, and Data Type Detection as desired. You can also select or clear the Skip files with errors checkbox at the bottom of the dialog box.

6. Select OK.

These steps are not helping much

[littlemojopuppy]

@amikm the real issue seems to be you don't understand how to use Power Query to clean your data...

https://docs.microsoft.com/en-us/learn/modules/automate-data-cleaning-power-query/

https://docs.microsoft.com/en-us/learn/modules/clean-data-power-bi/

[amikm]

@littlemojopuppy 
I am able to apply steps , but still it is not giving me desired result