PowerQuery and Expand Table Columns as Lists

[mcgeeks]

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 some columns are lists.  When expanding the Transform Data column those lists are causing problems.  It is expanding and creating separate rows for each item (customer) in the lists, and thus creating multiple rows for a single line item in the SharePoint list..  (one project listed on several rows).

For example, I have a Transform Data "Customer table" that lists customer name, ID, photo, email, etc.  When more than one customer is selected in SharePoint I have a list of comma separated names in SharePoint in that single cell.  But when the PowerBI Transform Data column is expanded I get many rows representing a single SharePoint List Item's Row.  Each Transform Data's row having a different name from the Customer list in said table.  Please see in the table below (from the PowerBI report), item 7 (shown far left) is listed twice with the only difference being that the two customers were broken out onto two rows rather than showing them on one row in a comma separated list.  The same goes for item 9.  Customer first names and middle initials are in the 4th column from the left showing that breakout.

What I need is that when the data is expanded in the Transform Data operation, it keeps only one ROW of data, with those multiple customers being listed as a comma separated LIST in a single cell rather than making it a multi row breakout. 

Any help resolving this Data Transformation expansion problem would be greatly appreciated.

Kind regards,

Scott

[m_dekorte]

Hi @mcgeeks,

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.

[WilliamAzevedo]

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!

[mcgeeks]

@m_dekorte ,

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

[m_dekorte]

Hi @mcgeeks,

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

[mcgeeks]

I tried both. 

Using this:

= Table.TransformColumns( #"Expanded PI",
{
{ "Customer", each Text.Combine( _, ", "), type text }
}
)

I went from this:

to this:

[mcgeeks]

I'll see what I can do with that and reply back.  Thanks.