Dynamically expand table columns

[andydtaylor]

Hi,

Is there a way that I can make the last line of this Power Query statement dynamic? i.e. always expand all columns?

let

// Get path
    Pathparameter = Excel.CurrentWorkbook(){[Name="sys_im_parameters"]}[Content],
    Pathparameter_value = Pathparameter{0}[Column1],

//main query

    Source = Folder.Files(Pathparameter_value),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from input_modules", each #"Transform File from input_modules"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from input_modules"}),
    #"Expanded Transform File from input_modules" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from input_modules", {"Source", "Month", "Description", "Amount", "Source_WS", "Entity", "Value_type", "Route", "Active flag", "Type"}, {"Source", "Month", "Description", "Amount", "Source_WS", "Entity", "Value_type", "Route", "Active flag", "Type"})
in
    #"Expanded Transform File from input_modules"

Thanks,

Andy

 

[VBA Geek]

You can use Table.ColumnNames function

So, without testing, the last two lines should be something like this:

ColNames = Table.ColumnNames( [COLOR=#333333]#"Removed Other Columns1"{0}[[/COLOR][COLOR=#333333]Transform File from input_modules] )[/COLOR][COLOR=#333333]
[/COLOR]#"Expanded Transform File from input_modules" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from input_modules", [B]ColNames [/B])
in
#"Expanded Transform File from input_modules"

 

[andydtaylor]

Hi,

If I can bug you for a little more of your time.

I have an issue in this scenario which is that I have a function which is compiling all of the source files in target folder which have a table called oExport. If appears to return a table of tables using the "Transform File from input_modules" function which is the 5th proper line down from the top.

Using the reference you proposed it reads the column names from the first table from this table of tables.

#"Removed Other Columns1"{0}[Transform File from input_modules]

The issue I have is that the column names are not uniform. between tables There is overlap but it's not 100% common and I'd like to have the flexibility. Is there another approach you can suggest?

Ultimately I would like to be able to drop any number of files with an o_Export table into my target folder and have my main model read these files tables without having to worry whether columns have been defined in the expandtablecolumn step. So even a list of unique columns from the first 3 tables would need the flexibility to include a 4th etc if present.

I really appreciate your time and insight

Thanks,

Andy

 

[andydtaylor]

Hi,

I've found a solution!

A piece of code far exceeding my own ability... I'm going to have to buy a book on M. Anyway here is a link to a function which will dynamically expand table columns:

https://blog.crossjoin.co.uk/2014/05/21/expanding-all-columns-in-a-table-in-power-query/


Cheers,

Andy

 

[VBA Geek]

Great stuff. In certain cases you could also use the Table.Combine function.