Forum Discussion
jcastr02
Post Prodigy
Post ProdigyUnpivoting into different columns
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 |
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 groupRowsListIt'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
3 Replies
- BA_Pete
Super User
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 groupRowsListIt'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
- amitchandak
jcastr02 , You can create a measure and use that in visual
concatenatex(Table, Table[Work Order Tag])
