Combing thousands of excel files using Power Query. Is my workflow correct?

Hello Anonymous 

try this function and let us know if there was an impact on performance

(ExcelFiles) =>
let
    Source = Excel.Workbook(ExcelFiles, null, true),
    Daily_Data_Entry_Sheet = Table.Buffer(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"

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy