cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
drorli
Helper I
Helper I

Expand Columns for Table with Unknown Column Names

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

 

1 REPLY 1
Icey
Community Support
Community Support

Hi @drorli ,

 

Thank you for your sharing! 

 

 

 

Best Regards,

Icey

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors