Combine multiple files in Power query editor

Question

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 · Answer

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.
&nbsp;
For Transform file M query
&nbsp;
let
    Source = (Parameter2) =&gt; 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
&nbsp;
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.

&nbsp;
For excel, you need to select the sheet (or) table (or) ... and the names has to be same in each excel file.&nbsp;I cannot download the excel file from the link, can you give public access to the excel file ...&nbsp;
&nbsp;
&nbsp;
For (b)&nbsp;
&nbsp;
    #"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] &lt;&gt; null and [Column1] &lt;&gt; "") 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"
&nbsp;
&nbsp;
Hope this helps!&nbsp;&nbsp;

littlemojopuppy · Answer

Hi&nbsp;amikm&nbsp;
&nbsp;
This should help.

amikm · Answer

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 · Answer

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

amikm · Answer

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 .&nbsp;Select&nbsp;Data&nbsp;&gt;&nbsp;Get Data&nbsp;&gt;&nbsp;From File&nbsp;&gt;&nbsp;From Folder. The&nbsp;Browse&nbsp;dialog box appears.Locate the folder containing the files you want to combine.A list of the files in the folder appears in the &lt;Folder path&gt; dialog box.&nbsp;Verify that all the files you want are listed.&nbsp;&nbsp;Select one of the commands at the bottom of the dialog box, for example&nbsp;&nbsp;Combine&nbsp;&gt;&nbsp;Combine &amp; Load. There are additional commands discussed in the section&nbsp;About all those commands.If you select any&nbsp;Combine&nbsp;command, The&nbsp;Combine Files&nbsp;dialog box appears. To change file settings, select each file from the&nbsp;Sample File&nbsp;box, set the&nbsp;File Origin,&nbsp;Delimiter, and&nbsp;Data Type Detection&nbsp;as desired. You can also select or clear the&nbsp;Skip files with errors&nbsp;checkbox at the bottom of the dialog box.Select&nbsp;OK.These steps are not helping much