Forum Discussion
Combing thousands of excel files using Power Query. Is my workflow correct?
Hi, I've built a power query function to extract data from excel files. The excel files are in the same format. The files are stored on a Onedrive folder. I've successfully queried the data usin...
Hello Anonymous
exactly, I only added Table.Buffer
So give it a test and let us know
Jimmy
Jimmy801
Community Champion
Community ChampionHello
The approach is all right. If you want you can post your custom function (and if possible a excel file), then i can have a look on this. But reading 2k excel files will take some time. By the way... how long does it take to refresh? Another suggestion is, to use a csv file instead, because they can easier be accessed. But i don't know if this could be a option for you.
All the best
Jimmy
The approach is all right. If you want you can post your custom function (and if possible a excel file), then i can have a look on this. But reading 2k excel files will take some time. By the way... how long does it take to refresh? Another suggestion is, to use a csv file instead, because they can easier be accessed. But i don't know if this could be a option for you.
All the best
Jimmy
- trebgatte
Most Valuable Professional
I'm not sure a custom function is needed to do this aggregation. If you are simply appending the files to each other, this is a built-in function. It may be faster to append all to one dataset and then group the results.
I have a blog post on how to use the out of box merge process here: https://marqueeinsights.com/how-to-merge-multiple-excel-files-with-power-bi/
Hope this helps.
--Treb, Power BI MVP
Thanks.
This is the master template that is being used. I'm extracting the indicator table (C14:E33). I have the function write the province, district, and facility and then pivot the indicators to columns for each excel file so I get a master table with each row representing each excel file.
It takes about 20-30 minutes to refresh.
Is CSV a feasible option?
Should I be doing this query in Power BI rather than have the excel doing the power query?
Would it be possible to autoamte the refresh without having to open the excel file and udpate the query?
- Jimmy801Hello
Could you please post the m code from the advanced editor?
JimmySure thing.
(ExcelFiles) => let Source = Excel.Workbook(ExcelFiles, null, true), Daily_Data_Entry_Sheet = Source{[Item="Daily_Data_Entry",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Daily_Data_Entry_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", type any}, {"DAILY MONITORING TOOL : Instruction >> : Please Select Province , District and then Facility ", type text}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}}), #"Select Columns" = Table.SelectColumns(#"Changed Type",{"DAILY MONITORING TOOL : Instruction >> : Please Select Province , District and then Facility ", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13"}), #"Removed Columns" = Table.RemoveColumns(#"Select Columns",{"Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13"}), #"Added Custom" = Table.AddColumn(#"Removed Columns", "Province", each #"Changed Type"[#"DAILY MONITORING TOOL : Instruction >> : Please Select Province , District and then Facility "]{2}), #"Added Custom1" = Table.AddColumn(#"Added Custom", "District", each #"Changed Type"[Column4]{2}), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Facility ", each #"Changed Type"[Column5]{2}), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Date", each #"Changed Type"[Column4]{54}), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom3",{{"Date", type text}}), #"Added Custom4" = Table.AddColumn(#"Changed Type1", "First Name", each #"Changed Type"[Column5]{52}), #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom4",{{"First Name", type text}}), #"Added Custom5" = Table.AddColumn(#"Changed Type2", "Last Name", each #"Changed Type"[Column5]{53}), #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom5",{{"Last Name", type text}}), #"Added Custom6" = Table.AddColumn(#"Changed Type3", "Name", each [First Name] & " "&[Last Name]), #"Changed Type4" = Table.TransformColumnTypes(#"Added Custom6",{{"Name", type text}}), #"Removed Columns1" = Table.RemoveColumns(#"Changed Type4",{"Column6", "First Name", "Last Name"}), #"Removed Top Rows" = Table.Skip(#"Removed Columns1",13), #"Renamed Columns" = Table.RenameColumns(#"Removed Top Rows",{{"DAILY MONITORING TOOL : Instruction >> : Please Select Province , District and then Facility ", "Indicator"}, {"Column4", "Male"}, {"Column5", "Female"}}), #"Kept Range of Rows" = Table.Range(#"Renamed Columns",0,19), #"Changed Type5" = Table.TransformColumnTypes(#"Kept Range of Rows",{{"Male", type text}, {"Female", type text}}), #"Replaced Value1" = Table.ReplaceValue(#"Changed Type5",null,"Null",Replacer.ReplaceValue,{"Male"}), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",null,"Null",Replacer.ReplaceValue,{"Female"}), #"Added Custom7" = Table.AddColumn(#"Replaced Value2", "Custom", each Text.Combine({[Indicator], "_Male", [Male]}, "-")), #"Added Custom8" = Table.AddColumn(#"Added Custom7", "Custom.1", each Text.Combine({[Indicator], "_Female", [Female]}, "-")), #"Removed Columns2" = Table.RemoveColumns(#"Added Custom8",{"Indicator", "Male", "Female"}), #"Added Custom9" = Table.AddColumn(#"Removed Columns2", "Indicator", each Text.Combine({[Custom], [Custom.1]}, ";")), #"Removed Columns3" = Table.RemoveColumns(#"Added Custom9",{"Custom", "Custom.1"}), #"Added Custom10" = Table.AddColumn(#"Removed Columns3", "Custom", each Text.Split([Indicator],";")), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom10", "Custom"), #"Removed Columns4" = Table.RemoveColumns(#"Expanded Custom",{"Indicator"}), #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns4",{{"Custom", "Indicator"}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns1", "Indicator", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, true), {"Indicator.1", "Indicator.2"}), #"Changed Type6" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Indicator.1", type text}, {"Indicator.2", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type6","_Male",null,Replacer.ReplaceValue,{"Indicator.2"}), #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Indicator.1]), "Indicator.1", "Indicator.2") in #"Pivoted Column"
