adding rows?

[topazz11]

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

[ryan_mayu · Accepted solution]

@topazz11 

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

[Ashish_Excel]

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?