Power Query Expand All Columns

cr731

Well-known Member
Joined
Sep 17, 2010
Messages
609
I'm using Power Query to consolidate a data table across multiple Excel workbooks. I'm using a Parameter table listing the source files and a function:

Code:
[
(filepath)=>
let
    Source = Excel.Workbook(File.Contents(filepath), null, true),
    TableofData= Source{[Item="MyTableName",Kind="Table"]}[Data]
in
    TableofData
/CODE]



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Full source name[/TD]
[TD]Get Excel Data[/TD]
[/TR]
[TR]
[TD][URL="file://\\networkpath\filename1.xlsm"]\\networkpath\filename1.xlsm[/URL][/TD]
[TD]=MyFunction([Full Source Name])[/TD]
[/TR]
[TR]
[TD][URL="file://\\networkpath\filename2.xlsm"]\\networkpath\filename2.xlsm[/URL][/TD]
[TD]=MyFunction([Full Source Name])[/TD]
[/TR]
[TR]
[TD][URL="file://\\networkpath\filename3.xlsm"]\\networkpath\filename3.xlsm[/URL][/TD]
[TD]=MyFunction([Full Source Name])[/TD]
[/TR]
</tbody>[/TABLE]


This produces a table like

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Full source name[/TD]
[TD]Get Excel Data[/TD]
[/TR]
[TR]
[TD][URL="file://networkpath/filename1.xlsm"]\\networkpath\filename1.xlsm[/URL][/TD]
[TD]Table[/TD]
[/TR]
[TR]
[TD][URL="file://networkpath/filename2.xlsm"]\\networkpath\filename2.xlsm[/URL][/TD]
[TD]Table[/TD]
[/TR]
[TR]
[TD][URL="file://networkpath/filename3.xlsm"]\\networkpath\filename3.xlsm[/URL][/TD]
[TD]Table[/TD]
[/TR]
</tbody>[/TABLE]

I then click to "Expand" the Tables returned.

The problem is... this creates a line that explicitly states each column to expand.  The tables I'm consolidating may have columns added/removed on a regular basis, so it needs to be dynamic and just expand "all" rather than specifying each column.

Is there a way using Table.ExpandTableColumn to expand all instead of requiring a list of each column?
 

cr731

Well-known Member
Joined
Sep 17, 2010
Messages
609
So my next attempt was to add a custom column containing a list of the column


Full source nameGet Excel DataExcel Table Columns
\\networkpath\filename1.xlsmTable=Table.ColumnNames([Get Excel Data])
\\networkpath\filename2.xlsmTable
\\networkpath\filename3.xlsmTable

<tbody>
</tbody>

This produces

Full source nameGet Excel DataExcel Table Columns
\\networkpath\filename1.xlsmTableList
\\networkpath\filename2.xlsmTableList
\\networkpath\filename3.xlsmTableList

<tbody>
</tbody>



I then try to use

Table.ExpandTableColumn(myTable, "Get Excel Data", [Excel Table Columns])

But this returns an error

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

So I try to change to

Table.ExpandTableColumn(myTable, "Get Excel Data", _ [Excel Table Columns])

But that also doesn't work.

Lastly I tried


Table.ExpandTableColumn(myTable, "Get Excel Data", each [Excel Table Columns])

And then get the error

Expression.Error: We cannot convert a value of type Function to type List.
Details:
Value=Function
Type=Type
 
Last edited:

ImkeF

Well-known Member
Joined
Feb 1, 2015
Messages
544
You're pretty close:
The Expand-command operates on the table-level and not on the row-level, so you need to create a list of column names that contains all column names of all rows. This can be done with List.Union:
Table.ExpandTableColumn(myTable, "Get Excel Data", List.Union(myTable[Excel Table Columns]))
 

Forum statistics

Threads
1,078,252
Messages
5,339,100
Members
399,278
Latest member
randomNumberGenerator2211

Some videos you may like

This Week's Hot Topics

Top