Forum Discussion
After Pivoting a Table I Need to Condense Rows
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
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 regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovicHI 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
3 Replies
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 regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovicIn Power Query, select the first 2 columns and do a 'Group By', put 3 aggregations of MIN (1 on each value column).
Good luck.
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
