Pivot row into column for each new occurrence

[Anonymous]

Hi

I have no problem with regular pivot column operation but is it easily possible to create a new column for each occurence of a value in a column?

Thanks

PBM

Sample data

id,name,contact

1,john,Mr Johnny

1,john,Mrs Johnny

2,fred,Mr Fred

3,jane,Mr Jane

3,jane,Mrs Jane

3,jane,Aunty Jane

Desired Output

id,name,contact1,contact2,contact3

1,john,Mr Johnny,Mrs Johnny,null

2,fred,Mr Fred,null,null

3,jane,MrJane,Mrs Jane,Aunty Jane

[wdx223_Daniel]

NewStep=Table.Combine(SampleData,"id",{"n",each #table({"id","name"}&List.Transform(List.Positions([contact]),each Number.ToText(_+1,"contact0")),{[contact]})})[n])

[KT_Bsmart2gethe · Accepted solution]

Hi @Anonymous ,

You can use Group-by to group text.

1. Group-by id, name and count the row. then update the code to something like

Table.Group(#"Promoted Headers", {"id", "name"}, {{"contact", each Text.Combine([contact],",")}})

2. split the contact column by delimiter

Regards

KT