Forum Discussion
PowerQuery and Expand Table Columns as Lists
Hi Anonymous,
Instead of expanding the list column, let's transform it's content.
Table.TransformColumns( PrevStepNameHere,
{
{ "ColumnName", each Text.Combine( _, ", "), type text }
}
)
This is assuming your list values are all of a text type.
When that's not the case or you can't be sure, include list transform, like below
Table.TransformColumns( PrevStepNameHere,
{
{ "ColumnName", each Text.Combine( List.Tansform( _, Text.From ), ", "), type text }
}
)
I hope this is helpful.
I'll see what I can do with that and reply back. Thanks.
I tried both.
Using this:
= Table.TransformColumns( #"Expanded PI",
{
{ "Customer", each Text.Combine( _, ", "), type text }
}
)I went from this:
to this:
When I get in transform, and select the cell in question prior to expanding or transforming it, I see this...
Hi Anonymous,
Okay so you have a column with nested tables not lists...
You can still use this approach it just needs a little tweek, for example
if you need a single column from that table, it would look like this:
Table.TransformColumns( PrevStepNameHere, {{"Customer", each Text.Combine( _[value], ", "), type text }})or if you want to extract many columns, it can look like this
Table.TransformColumns( PrevStepNameHere, { {"Customer", each Record.FromList( List.Transform( Table.ToColumns(_), (x)=> Text.Combine( List.Transform( x, Text.From ), ", ")), Table.ColumnNames(_) ) } } )This will return a record, from which you can select fields.
Hope this is helpful
In my case, a bad choice for lack of experience: I had a column set to Table.ExpandListColumn (my data source is a SharePoint list) what gave me some duplicate rows, a bad thing since I'm calculating hours. Just followed the first code and it worked imediately.
Thank you very much!
