Forum Discussion
Conversion from a Row to Multiple Columns
Hi,
This might be extremely basic questions for the expert user. But pretty tough for me. I have tried pivoting and unpivoting, however, couldn't get throug.
The original Dataset
| Date | Name | Value | Annual Increment |
| 01-Jul-22 | G | 19 | 1 |
| 02-Jul-22 | G | 24.5 | 2 |
| 03-Jul-22 | G | 30 | 3 |
| 04-Jul-22 | G | 35.5 | 4 |
| 05-Jul-22 | G | 41 | 5 |
| 01-Jul-22 | O | 46.5 | null |
| 02-Jul-22 | O | 52 | null |
| 03-Jul-22 | O | 57.5 | null |
| 04-Jul-22 | O | 63 | null |
| 05-Jul-22 | O | 68.5 | null |
| 01-Jul-22 | C | 74 | null |
| 02-Jul-22 | C | 79.5 | null |
| 03-Jul-22 | C | 85 | null |
| 04-Jul-22 | C | 90.5 | null |
| 05-Jul-22 | C | 96 | null |
The Required Shape
| Date | G | O | C | Annual Increment |
| 01-Jul-22 | 19 | 46.5 | 74 | 1 |
| 02-Jul-22 | 24.5 | 52 | 79.5 | 2 |
| 03-Jul-22 | 30 | 57.5 | 85 | 3 |
| 04-Jul-22 | 35.5 | 63 | 90.5 | 4 |
| 05-Jul-22 | 41 | 68.5 | 96 | 5 |
Thank you for your assistance.
Regards,
Usman
Hi Anonymous ,
I created a sample pbix file(see attachment), please check if that is what you want...
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc47DoQwDATQu7iGFfEnkHoLJJo9QJQb0HL/jS2hYIvC4+JppKkV0nxc54wIE+z9UtGANlVAL8gf0WdG3mjRMOEgYi02E2+ceojJY8VPJVsrzlASvIECrKPDnjLdIAG20XlM+PZb+WWAQRkd8rS9DVAoy+hIoGzQ/g==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Name = _t, Value = _t, #"Annual Increment" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Name", type text}, {"Value", type number}, {"Annual Increment", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Annual Increment"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Name]), "Name", "Value", List.Sum), #"Added Index" = Table.AddIndexColumn(#"Pivoted Column", "Annual Increment", 1, 1, Int64.Type) in #"Added Index"Best Regards
2 Replies
Hi Anonymous ,
I created a sample pbix file(see attachment), please check if that is what you want...
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc47DoQwDATQu7iGFfEnkHoLJJo9QJQb0HL/jS2hYIvC4+JppKkV0nxc54wIE+z9UtGANlVAL8gf0WdG3mjRMOEgYi02E2+ceojJY8VPJVsrzlASvIECrKPDnjLdIAG20XlM+PZb+WWAQRkd8rS9DVAoy+hIoGzQ/g==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Name = _t, Value = _t, #"Annual Increment" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Name", type text}, {"Value", type number}, {"Annual Increment", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Annual Increment"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Name]), "Name", "Value", List.Sum), #"Added Index" = Table.AddIndexColumn(#"Pivoted Column", "Annual Increment", 1, 1, Int64.Type) in #"Added Index"Best Regards
Hi
The only way to get to your desired result is to have the Increment values in place of the nulls? What should the logic be for plugging the increment values in the null cells? Please clarify.
