Import 3 folders in a time & Combine

[Chanleakna123]

Hi all , 

i have 3 folders in every month , and in each folder have 40 excel files which are in the same format and template as well as the header are the same. 

Right now , i Import 1 by 1 of each folder to PBI ( Transform & Combine ) . Every month i import through folder for 3 times. And then i gonna use APPEND Feature in BI to put it together. 

Do you have any idea how to Import 3 Folders every month at the same time and Combined all those excel file together ? 

Thx 

[PhilipTreacy]

Hi @Chanleakna123 

Each step must have a unique name.  You have 4 steps called Source.

Double check my code carefully. The first 4 steps are called

Source1

Source2

Source3

Source

Just change the step names to match these and it should work.

Regards

Phil

[Chanleakna123]

hi @PhilipTreacy , i did the same , though i can't click Done and proceed further , seems like i miss sth there. 

Can you help me on this ? 

[PhilipTreacy]

Don't @Chanleakna123

Do not worry. If you have 3 folders to upload files since then we will call those folders Source1, Source2 and Source3.

All you need to do is enter the correct path/folder name for each of those sources. Here is the modified query.

let
  Source1 = Folder.Files("C:\Users\hchanleakna\Desktop\Power BI\93-Incentive\BOS Data\PNP Nov"),
  Source2 = Folder.Files("D:\Data\Folder2"),
  Source3 = Folder.Files("D:\Data\Folder3"),
  Source = Table.Combine({Source1, Source2, Source3}),
  #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[ Hidden]? <> true),
  #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (15)", each   #"Transform File (15)"([Content])),
  #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
  #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (15)"}),
  #"Removed Errors1" = Table.RemoveRowsWithErrors(#"Removed Other Columns1", {"Transform File (15)"}),
  #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Errors1", "Transform File (15)", Table.ColumnNames(#"Transform File (15)"(#"Sample File (15)"))),
  #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Document#(lf)Number", type text}, {"Delivery#(lf)Date", type date}, {"Order#(lf)Date", type date}, {"Del#(lf)Route", type text}, {"#(lf)Driver", type text}, {"#(lf)Vehicle", type text}, {"#(lf)Helper1", type any}, {"#(lf)Helper2", type any}, {"#(lf)Helper3", type any}, {"#(lf)Sales Rep", type text}, {"Outlet#(lf)Number", Int64.Type}, {"Customer Code", Int64.Type}, {"#(lf)Outlet Name", type text}, {"Art#(lf)No.", Int64.Type}, {"#(lf)Article Name", type text}, {"Qty#(lf)Ordered", Int64.Type}, {"Qty#(lf)Delivered", type number}, {"Return#(lf)Qty", Int64.Type}, {"Fulfill#(lf)Rate", Int64.Type}, {"Returns #(lf)Reason", type any}, {"Original#(lf)Order Qty", Int64.Type}, {"FOC Adjustment#(lf)Description#(lf)", type any}, {"FOC#(lf)Qty", Int64.Type}, {"Net#(lf)Quantity", Int64.Type}, {"#(lf)Price", type number} , {"DiscountAmount", type number}, {"Net#(lf)Amount", type number}, {"Transaction#(lf)Status", type text}, {"#(lf)Delivery Instructions", type any}, {"Batch#(lf)Date", type date}, {"OM01 #(lf)SalesRep Name#(lf)", type any}, {"SFA #(lf)Order Number#(lf)", type text}, {"Customer Order Number", type text}})
in
  #"Changed Type"

Best regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking Thumbs Up.

[PhilipTreacy]

Hi @Chanleakna123 

If you are loading files from 3 folders let's call them Source1, Source2 and Source 3 - you can load them all at the same time then append them using this code.  Of course, change the folder path to suit yours 🙂

let
    Source1 = Folder.Files("D:\Data\Folder1"),
    Source2 = Folder.Files("D:\Data\Folder2"),
    Source3 = Folder.Files("D:\Data\Folder3"),
    Source = Table.Combine({Source1, Source2, Source3})
in
    Source

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Chanleakna123]

@PhilipTreacy  , hi  , i have below source , and just change the Name of the source , 

may i know how to connect it each other ? sorry , i am not good with this 

let
Source = Folder.Files("C:\Users\hchanleakna\Desktop\Power BI\93-Incentive\BOS Data\PNP Nov"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (15)", each #"Transform File (15)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (15)"}),
#"Removed Errors1" = Table.RemoveRowsWithErrors(#"Removed Other Columns1", {"Transform File (15)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Errors1", "Transform File (15)", Table.ColumnNames(#"Transform File (15)"(#"Sample File (15)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Document#(lf)Number", type text}, {"Delivery#(lf)Date", type date}, {"Order#(lf)Date", type date}, {"Del#(lf)Route", type text}, {"#(lf)Driver", type text}, {"#(lf)Vehicle", type text}, {"#(lf)Helper1", type any}, {"#(lf)Helper2", type any}, {"#(lf)Helper3", type any}, {"#(lf)Sales Rep", type text}, {"Outlet#(lf)Number", Int64.Type}, {"Customer Code", Int64.Type}, {"#(lf)Outlet Name", type text}, {"Art#(lf)No.", Int64.Type}, {"#(lf)Article Name", type text}, {"Qty#(lf)Ordered", Int64.Type}, {"Qty#(lf)Delivered", type number}, {"Return#(lf)Qty", Int64.Type}, {"Fulfill#(lf)Rate", Int64.Type}, {"Returns #(lf)Reason", type any}, {"Original#(lf)Order Qty", Int64.Type}, {"FOC Adjustment#(lf)Description#(lf)", type any}, {"FOC#(lf)Qty", Int64.Type}, {"Net#(lf)Quantity", Int64.Type}, {"#(lf)Price", type number}, {"DiscountAmount", type number}, {"Net#(lf)Amount", type number}, {"Transaction#(lf)Status", type text}, {"#(lf)Delivery Instructions", type any}, {"Batch#(lf)Date", type date}, {"OM01 #(lf)SalesRep Name#(lf)", type any}, {"SFA #(lf)Order Number#(lf)", type text}, {"Customer Order Number", type text}})
in
#"Changed Type"