Forum Discussion
adding rows?
I have a data table with current and prior data.
Is it possible to add variance data between the two in the table or create a table together?
| Type | Name | value |
| Current | aa | 9 |
| Current | bb | 8 |
| Current | cc | 8 |
| Current | dd | 9 |
| Current | ee | 7 |
| Prior | aa | 4 |
| Prior | bb | 5 |
| Prior | ee | 6 |
| Prior | dd | 7 |
| Prior | cc | 5 |
| Prior | dd | 4 |
| Variance | aa | 1 |
| Variance | bb | 3 |
| Variance | ee | 1 |
| Variance | cc | 3 |
| Variance | dd | 5 |
you can do this in PQ
1. group by table
2. pivot table
3. create variance column
4. unpivot table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCqksSFXSUfJLzAVRZYk5palKsTrRSs6lRUWpeSVAscREIGGJJpiUBCQs0ASTk7EIpqRg0Z4KsswcLBhQlJlfBLPGBEUIbIkpihBYoxmKENgCVLPADjHFanwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Type", type text}, {"Name", type text}, {"value", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"Type", "Name"}, {{"value", each List.Sum([value]), type nullable number}}),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Type]), "Type", "value"),
#"Added Custom" = Table.AddColumn(#"Pivoted Column", "variance", each [Current]-[Prior]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Custom", {"Name"}, "Attribute", "Value"),
#"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Attribute", Order.Ascending}})
in
#"Sorted Rows"pls see the attachment below
2 Replies
you can do this in PQ
1. group by table
2. pivot table
3. create variance column
4. unpivot table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCqksSFXSUfJLzAVRZYk5palKsTrRSs6lRUWpeSVAscREIGGJJpiUBCQs0ASTk7EIpqRg0Z4KsswcLBhQlJlfBLPGBEUIbIkpihBYoxmKENgCVLPADjHFanwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Type", type text}, {"Name", type text}, {"value", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"Type", "Name"}, {{"value", each List.Sum([value]), type nullable number}}),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Type]), "Type", "value"),
#"Added Custom" = Table.AddColumn(#"Pivoted Column", "variance", each [Current]-[Prior]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Custom", {"Name"}, "Attribute", "Value"),
#"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Attribute", Order.Ascending}})
in
#"Sorted Rows"pls see the attachment below
Hi,
Why do you want to do this? Why not show name in the row labels, type in the column labels and variance as the last column? Also, don't you have a date column?
