After Pivoting a Table I Need to Condense Rows

[maxs3]

Hello,

I pivoted a table, and it resulted in many "NULL" values in columns, I am trying to condense/aggregate (not sure right word here) to a single ROW instead of 3 ROWS.

What I have

Group	Name	Value1	Value2	Value3
1	1	41	NULL	NULL
1	1	NULL	415	NULL
1	1	NULL	NULL	152
1	2	156	NULL	NULL
1	2	NULL	332	NULL
1	2	NULL	NULL	121.6
2	1	156	NULL	NULL
2	1	NULL	156	NULL
2	1	NULL	NULL	156
2	2	156	NULL	NULL
2	2	NULL	156	NULL
2	2	NULL	NULL	156

What I am trying to get

Group	Name	Value1	Value2	Value3
1	1	41	415	152
1	2	156	332	121.6
2	1	156	156	156
2	2	156	156	156

Thank you!

M

[selimovd · Accepted solution]

Hey @maxs3 ,

you can do that in Power Query.

Go to the Transform tab and use the "Group by" function:

Then you can group by "Group" and "Name" and sum by the value columns:

Afterwards, the result is like you wanted to be:

Check my solution in the advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWMTEOEX6uMDo2J1EJJQcRNDUzyyUMrQ1AguawTmm+Ew2QghbmxshEcWZrKRoZ4ZWN4IajN2s41Q3YWkCIsskiKYLG5XG6G6C4vJWFwNMjkWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, Name = _t, Value1 = _t, Value2 = _t, Value3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", Int64.Type}, {"Name", Int64.Type}, {"Value1", type number}, {"Value2", type number}, {"Value3", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Group", "Name"}, {{"Value1", each List.Sum([Value1]), type nullable text}, {"Value2", each List.Sum([Value2]), type nullable text}, {"Value3", each List.Sum([Value3]), type nullable text}})
in
    #"Grouped Rows"

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous · Accepted solution]

HI @maxs3,

You can enter to query editor to replace all 'NULL' string to the null value, then you can use the 'fill down' feature to expand these three field values.
After these steps, you only need to add a filter on your table to keep records which three fields not equal to blank.
Regards,
Xiaoxin Sheng

[selimovd · Accepted solution]

Hey @maxs3 ,

you can do that in Power Query.

Go to the Transform tab and use the "Group by" function:

Then you can group by "Group" and "Name" and sum by the value columns:

Afterwards, the result is like you wanted to be:

Check my solution in the advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWMTEOEX6uMDo2J1EJJQcRNDUzyyUMrQ1AguawTmm+Ew2QghbmxshEcWZrKRoZ4ZWN4IajN2s41Q3YWkCIsskiKYLG5XG6G6C4vJWFwNMjkWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, Name = _t, Value1 = _t, Value2 = _t, Value3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", Int64.Type}, {"Name", Int64.Type}, {"Value1", type number}, {"Value2", type number}, {"Value3", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Group", "Name"}, {{"Value1", each List.Sum([Value1]), type nullable text}, {"Value2", each List.Sum([Value2]), type nullable text}, {"Value3", each List.Sum([Value3]), type nullable text}})
in
    #"Grouped Rows"

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[HotChilli]

In Power Query, select the first 2 columns and do a 'Group By', put 3 aggregations of MIN (1 on each value column).

Good luck.