Expand column with a table in it containing variable numbers of columns

K

kerygma

New member
Joined
Jul 11, 2016
Messages
19
Reaction score
0
Points
0
Just trying to write a query where sometimes the table being imported has 33 columns, sometimes 40, etc. When I click the step to expand that column containing the table, it hard codes each column into the query and misses the extra columns if there are more the next time I run the query on a new table (pulling it from a folder). So far I came across a custom function which was pretty long, looking to see if there is something simpler I'm missing.
Thanks!
 
let
Source = Folder.Files("C:\Users\Documents\PowerQuery\input1"),
#"Added Custom2" = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"Name", "Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Kind] = "Table")),
#"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"Name", "Data"}),

----> Here's the custom function someone made "ExpandAll"
Output = ExpandAll(#"Removed Other Columns1"),

I'm using Excel 2010... and an un-updated version of PQ per IT restrictions/bureaucracy here at work. Yuck.
Thanks!
 
Okay, so Excel files then. This makes it a bit tricky. (With txt or csv it's easy.)

Tell me, will all files always have the same columns at the beginning, or no?

What I'm thinking is that are a couple of M functions that could be useful here:
=Table.ColumnCount([Data]) will return the number of columns in each sub table

You could then sort (descending) by that column to get the highest one to the top. Then you could extract the names of the column headers from that table using:
=Table.ColumnNames(#"Step Name"[Data]{0})

Something that looks like this:

Code: 
let[COLOR=#333333]    Source = Folder.Files("C:\Users\Documents\PowerQuery\input1"),[/COLOR]
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Excel.Workbook([Content])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Name", "Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Expanded Custom",{"Data"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Other Columns1", "ColumnCount", each Table.ColumnCount([Data])),
    #"Sorted Rows" = Table.Sort(#"Added Custom1",{{"ColumnCount", Order.Descending}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Sorted Rows", "Data", Table.ColumnNames(#"Sorted Rows"[Data]{0}),Table.ColumnNames(#"Sorted Rows"[Data]{0}))
in
    #"Expanded Data"

Let me know if that helps,
 
Thanks Ken.
My goal is pretty simple (just expand the one column with table data and then merge all those columns back into one column). I don't care about preserving the headers or anything, they are always just Column1, Column2, Column3... etc. I'm going to try your M code and see if it does what I need. Currently my power query tab buttons are all greyed out except for the update button... first time this has happened. I'm going to restart and see if that fixes it.
 
You must log in or register to reply here.
Back
Top