Power Query Expand All Columns

[cr731]

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:

[
(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]

So my next attempt was to add a custom column containing a list of the column


[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Full source name[/TD]
[TD]Get Excel Data[/TD]
[TD]Excel Table Columns[/TD]
[/TR]
[TR]
[TD]\\networkpath\filename1.xlsm[/TD]
[TD]Table[/TD]
[TD]=Table.ColumnNames([Get Excel Data])[/TD]
[/TR]
[TR]
[TD]\\networkpath\filename2.xlsm[/TD]
[TD]Table[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]\\networkpath\filename3.xlsm[/TD]
[TD]Table[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This produces

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



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

 

[ImkeF]

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]))

 

[Mitchell_Collier]

I have what seems to be very similar type of issue. I have three queries. The first one needs to expand columns with names of months that change due to a 3 month lookback. The first query is trying to merge in query 2 (Item quantity) then expand all columns. So I tried using the List.Union to identify the three months that will need to be expanded. I get an error like:

Expression.Error: We cannot convert the value "February" to type List.
Details:
Value=February
Type=[Type]


Since the error says "value" I have even tried it with numbers only. Ive tried formatting all items in the same format and still get similar errors.


1. Main Query Needing Expanding. This query has ItemNumbers that match up to number 2 query that I would like to bring in number 2's quantity used in a given month.

CODE::

#"Merged Queries2" = Table.NestedJoin(#"Replaced Value2", {"ItemNumber"}, #"Item_Quantity (2)", {"ItemNumber"}, "Item_Quantity (2)", JoinKind.LeftOuter),
#"Expanded Item_Quantity (2)" = Table.ExpandTableColumn(#"Merged Queries2", "Item_Quantity (2)", List.Union(Month[Month]))
in
#"Expanded Item_Quantity (2)"

Main issue somewhere in: "Table.ExpandTableColumn(#"Merged Queries2", "Item_Quantity (2)", List.Union(Month[Month]))"

2. Example of Query “Item_Quantity (2)”. I am trying to bring these numbers into my main query.

ItemNumber	February	March	April
23432	1	2	3
234323	1	2	3
15489484	1	2	3

3. Example of Query Table named “Month” with column named “Month” as well. I am using this as a list for columns to expand.

Month

February

March

April