Get Data- Blank Query - Advanced editor- Paste the below.
let
Source = Folder.Files("C:\*Your folder directory of files*"),
#"Get the files" = Table.SelectRows(Source, each [Extension] = ".csv" or [Extension] = ".txt" or Text.StartsWith([Extension], ".xls")),
#"Removed Other Columns" = Table.SelectColumns(#"Get the files",{"Content", "Name", "Extension"}),
Trans1 = Table.AddColumn(#"Removed Other Columns", "Custom", each if Text.StartsWith([Extension], ".xls") then Excel.Workbook( [Content]) else null),
Trans2 = Table.ExpandTableColumn(Trans1, "Custom", {"Name", "Data", "Item", "Kind"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind"}),
#"The Formula" = Table.AddColumn(Trans2, "Custom", each if [Extension] = ".csv" then
Table.PromoteHeaders(Csv.Document([Content]))
else if [Extension] = ".txt"
then Table.PromoteHeaders(Csv.Document([Content],null,"," ))
else if [Custom.Kind] <> "Table"
then Table.PromoteHeaders([Custom.Data])
else
[Custom.Data]),
#"Removed Columns" = Table.RemoveColumns(#"The Formula",{"Content", "Custom.Data"}),
#"Almost there" = Table.AddColumn(#"Removed Columns", "Custom.1", each Table.RowCount([Custom])),
MyList = Table.ToList(Table.RemoveColumns(Table.Distinct(Table.ExpandListColumn(Table.AddColumn(#"Almost there", "ColumnHeaders", each Table.ColumnNames([Custom])), "ColumnHeaders"),{"ColumnHeaders"}),{"Name", "Extension", "Custom.Name", "Custom.Item", "Custom.Kind", "Custom", "Custom.1"})),
#"Here we go" = Table.RenameColumns(#"Almost there",{{"Custom.1", "Total Rows"},{"Custom.Kind", "Kind"}, {"Custom.Item", "Item Name"}, {"Custom.Name", "Sheet Name"}, {"Name", "File Name"}}),
Expanded = Table.ExpandTableColumn(#"Here we go", "Custom",MyList)
in
Expanded
Taken from -
https://powerpivotpro.com/2015/01/power-query-for-excel-combine-multiple-files-of-different-file-typ...
