I seached Google for "power query expand all columns".
Top search result is from 2014 and seemed really complex, second item found did not seem simpler and only 3rd result was from this forum, by @ImkeF from 2018. (note-to-self, 1st stop to seek answers should be this forum)
In short, the trick is to get the list of column names (Table.ColumnNames), on the column which holds the table of unknown column names (using Table.Combine).
It you have a Table (say it is called TABLE_OF_TABLES) and it has a column that each value is a Table with unknown columns names (say column is names table_column), you can expand it using:
Table.ExpandTableColumn(
TABLE_OF_TABLES,
"table_column",
Table.ColumnNames(Table.Combine(TABLE_OF_TABLES[table_column])),
Table.ColumnNames(Table.Combine(TABLE_OF_TABLES[table_column]))
)
Since it took me some time to get is right and since this looks like a pretty common task I am posting the functions I implemented. Hope others will benefit from it.
Function GetSheetDataFromFiles
= (sheetName) =>
let
fileNames = FILE_NAMES, // A table with a single column called "Data Source File Name", each entry is a file name (full path)
readFileData = Table.AddColumn(fileNames,
"fileData",
each GetSheetData([Data Source File Name], sheetName)
),
expandFileData = Table.ExpandTableColumn(
readFileData,
"fileData",
Table.ColumnNames(Table.Combine(readFileData[fileData])),
Table.ColumnNames(Table.Combine(readFileData[fileData]))
),
removeFileName = Table.RemoveColumns(expandFileData,{"Data Source File Name"})
in
removeFileName
Function GetSheetData
= (fileName, sheetName) =>
let
Source = Excel.Workbook(File.Contents(fileName), null, true),
allData = Source{[Item=sheetName,Kind="Sheet"]}[Data],
promotedHeaders = Table.PromoteHeaders(allData, [PromoteAllScalars=true])
in
promotedHeaders
