Forum Discussion
Solved
Unpivoting 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. T...
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
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