Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
Proud to be a Super User!
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 ?
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.
Proud to be a Super User!
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.
Proud to be a Super User!
@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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |