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
m_dekorte's avatar
m_dekorte
Resident Rockstar
3 years ago

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.

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