Unpivoting into different columns

[jcastr02]

I am trying to group a column into one value that have the same work order, see example below.  Work order 29999 all seperate line items, but want to group the work order tag reason into one cell.  Trying to manipulate this in power query...

Current

Work Order Number	Work Order Tag Reason	Reigon	Site
12340		Reigon 6	Store
25789		Reigon 6	Store
29999	FIX SLA 1	Reigon 9	DC
29999	FIX SLA 2	Reigon 9	DC
29999	FIX SLA 3	Reigon 10	DC

Expected Result 

Work Order Number	Work Order Tag Reason	Reigon	Site
12340		Reigon 6	Store
25789		Reigon 6	Store
29999	FIX SLA 1, FIX SLA 2, FIX SLA 3	Reigon 9	DC

[BA_Pete · Accepted solution]

Hi @jcastr02 ,

Here you go:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjFQ0lFSAOKg1Mz0/DwFMyAzuCS/KFUpVidaycjU3MISrwJLIACKuHlGKAT7OCoYIhSChF2csaoyIkqVMaaqWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Work Order Number" = _t, #"Work Order Tag Reason" = _t, Reigon = _t, Site = _t]),
    chgAllTypes = Table.TransformColumnTypes(Source,{{"Work Order Number", type text}, {"Work Order Tag Reason", type text}, {"Reigon", type text}, {"Site", type text}}),
    groupRowsList = Table.Group(chgAllTypes, {"Work Order Number", "Reigon", "Site"}, {{"Work Order Tag Reason", each Text.Combine([Work Order Tag Reason], ", "), type nullable text}})
in
    groupRowsList

It's a basic Power Query group function, but you switch out one of the standard operators from the GUI to the Text.Combine function instead.

Gives me this:

Pete

[BA_Pete]

Hi @jcastr02 ,

Did this work ok for you? I saw that you kudoed but didn't mark as the solution.

If this isn't working for you then let me know what's wrong and I'll have another look.

Pete

[amitchandak]

@jcastr02 , You can create a measure and use that in visual

concatenatex(Table, Table[Work Order Tag])