Variable number of excel files into append query in PowerQuery

[ChristianStokes]

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?

[v-yuezhe-msft · Accepted solution]

@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

[ChristianStokes]

This worked  perfectly! Thank you!

[ChristianStokes]

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"