Expand column containing list of records

[aaumond]

Hello,

I'm trying to expand a column which contains list of records on each cell in Power Query (for  example "Type de prestation" column) :

 

Each record of lists is organized as follow (self, value and id) :

 

The idea would be to concatenate the values ​​"value" contained in each record of the list for each cell, delimiting them with semicolons.

For example, for the third row of the table, the celle "Type de prestation" would contain "PMO;Architecture".

I don't know if I'm clear... Have you ever done that? If yes, how?

Thanks in advance.

[OwenAuger]

Hi @aaumond

You should create a new step with code like this.

PreviousStep should refer to the previous step in your existing query.

This is how the code would look in the advanced editor. If you enter in the formula bar by clicking fx, then leave out the step name CombineValueFromRecords.

CombineValueFromRecords =
  Table.TransformColumns(
    PreviousStep,
    {
      {
        "Type de prestation", 
        each Combiner.CombineTextByDelimiter(";")(
	  List.Transform( _, each [value] )
        ),
	type text
      }
    }
  )

Regards,

Owen

[aaumond]

Hi @OwenAuger

Thank you so much for the solution! It works.

Just a small detail: when a cell is empty, an error is displayed because it can not convert the null value to a list. How can I avoid this kind of error?

Regards,

Anthony

[OwenAuger · Accepted solution]

Hi Anthony,

That's good news.

To catch errors in general, you can use try/otherwise.

In this case, this should work:

= Table.TransformColumns(#"Développer Type de prestation",
{{"Phase projet", each try Combiner.CombineTextByDelimiter(";")(List.Transform( _ , each [value]))
otherwise null, type text}})