Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ChristianStokes
Regular Visitor

Variable number of excel files into append query in PowerQuery

Can't exactly find where the best place to pose this question is, so I am hoping to get either an answer or a reference to a better forum.

 

I have an SSRS subscription that puts a varying number of excel workbooks into a common location. I am using PowerQuery in excel to load and append them to make a master table. It's a very hardcoded solution. I can get the file names using the query that drives the data-driven subscription, but I can't figure out a way to have PowerQuery cycle through the values to load the data.

 

Any thoughts?

 

1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee

@ChristianStokes,

Why not put all excel files into a folder and connect to the folder using Power Query? This way, you don't need to manually append query for these excel files one by one.

There is a blog for your reference.
https://www.myexcelonline.com/blog/consolidate-multiple-excel-workbooks-using-power-query/

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yuezhe-msft
Employee
Employee

@ChristianStokes,

Why not put all excel files into a folder and connect to the folder using Power Query? This way, you don't need to manually append query for these excel files one by one.

There is a blog for your reference.
https://www.myexcelonline.com/blog/consolidate-multiple-excel-workbooks-using-power-query/

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

This worked  perfectly! Thank you!

In case anyone is interested, here's the code that does it:

let
FilePath = Text.From(Excel.CurrentWorkbook(){[Name="Control_Table"]}[Content]{0}[UNCPath]),
Source = Folder.Files(FilePath),
#"Filtered Rows" = Table.SelectRows(Source, each not Text.Contains([Name], "Christian") and not Text.Contains([Name], "Stacey") and not Text.Contains([Name], "~")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content", "Name"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "import", each Excel.Workbook([Content])),
#"Expanded import" = Table.ExpandTableColumn(#"Added Custom", "import", {"Data"}, {"import.Data"}),
#"Expanded import.Data" = Table.ExpandTableColumn(#"Expanded import", "import.Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18"}, {"import.Data.Column1", "import.Data.Column2", "import.Data.Column3", "import.Data.Column4", "import.Data.Column5", "import.Data.Column6", "import.Data.Column7", "import.Data.Column8", "import.Data.Column9", "import.Data.Column10", "import.Data.Column11", "import.Data.Column12", "import.Data.Column13", "import.Data.Column14", "import.Data.Column15", "import.Data.Column16", "import.Data.Column17", "import.Data.Column18"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded import.Data",{"Content"}),
#"Filtered Top Rows" = Table.SelectRows(#"Removed Columns", each ([import.Data.Column1] <> null and [import.Data.Column1] <> "Agency Party Number")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Top Rows",{{"import.Data.Column1", "Agency Party Number"}, {"import.Data.Column2", "Agency Party Name"}, {"import.Data.Column3", "Policy Number"}, {"import.Data.Column4", "Account ID"}, {"import.Data.Column5", "Receivable Type Code"}, {"import.Data.Column6", "Future"}, {"import.Data.Column7", "0-30"}, {"import.Data.Column8", "31-60"}, {"import.Data.Column9", "61-90"}, {"import.Data.Column10", "Over 90"}, {"import.Data.Column11", "Total"}, {"import.Data.Column12", "Total Less Future"}, {"import.Data.Column13", "Suspense Amount"}, {"import.Data.Column14", "Source System"}, {"import.Data.Column15", "Company Name"}, {"import.Data.Column16", "As Of Date"}, {"import.Data.Column17", "Account Has Suspense"}, {"import.Data.Column18", "Notes"}, {"Name", "Account Rep"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",".xlsx","",Replacer.ReplaceText,{"Account Rep"})
in
#"Replaced Value"

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors