Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

PowerQuery and Expand Table Columns as Lists

Hi,    I'm Transforming Data in PowerBI that I'm getting from a SharePoint list.  Here's the rub: Some of the coIumns in the PowerBI Transform Data operation contain tables, and inside those tables...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

m_dekorte ,

 

When I get in transform, and select the cell in question prior to expanding or transforming it, I see this...

 

 

m_dekorte's avatar
m_dekorte
Resident Rockstar
3 years ago

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