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.
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?
Solved! Go to 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,
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
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"
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.