Forum Discussion
Merging rows by new field name
- 4 years ago
The first step would be to sort your data by the report type, so the outcome fields are all the same.
Second, use group by, and concatenate all of the report type values in the resulting group:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKL0gtKqlUMFTSUQqpLEhVcAIyDA0MgKSxqYFSrA42NY6oamIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Property = _t, Type = _t, #"Value 1" = _t, #"Value 2" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Property", type text}, {"Type", type text}, {"Value 1", Int64.Type}, {"Value 2", Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Type", Order.Ascending}}), #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Property"}, {{"All Rows", each _, type table [Property=nullable text, Type=nullable text, Value 1=nullable number, Value 2=nullable number]}, {"New Type", each Text.Combine([Type], " - "), type nullable text}}) in #"Grouped Rows"Oof, that posts ugly. Click the dropdown on your type field. Sort it ascending.
Right click your Property field, and choose 'Group By'.
Select advanced, and create one field for 'All Rows' (assuming you want to preserve the rows)Create a second one of type 'Sum' on your 'Type' field. - This will return errors, because you can't sum text.
Go into the generated code of the step, and replace the sum bit with:
{"New Type", each Text.Combine([Type], " - "), type nullable text}
Power Query seems to be the only way to do this given the scale
The first step would be to sort your data by the report type, so the outcome fields are all the same.
Second, use group by, and concatenate all of the report type values in the resulting group:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKL0gtKqlUMFTSUQqpLEhVcAIyDA0MgKSxqYFSrA42NY6oamIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Property = _t, Type = _t, #"Value 1" = _t, #"Value 2" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Property", type text}, {"Type", type text}, {"Value 1", Int64.Type}, {"Value 2", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Type", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Property"}, {{"All Rows", each _, type table [Property=nullable text, Type=nullable text, Value 1=nullable number, Value 2=nullable number]}, {"New Type", each Text.Combine([Type], " - "), type nullable text}})
in
#"Grouped Rows"
Oof, that posts ugly. Click the dropdown on your type field. Sort it ascending.
Right click your Property field, and choose 'Group By'.
Select advanced, and create one field for 'All Rows' (assuming you want to preserve the rows)
Create a second one of type 'Sum' on your 'Type' field. - This will return errors, because you can't sum text.
Go into the generated code of the step, and replace the sum bit with:
{"New Type", each Text.Combine([Type], " - "), type nullable text}