How to create Calculated table?

[vynguyen]

Current table:

I want my table to look like the one below, what formula should I use?

Tks

[vynguyen]

Tks

[nandic · Accepted solution]

@vynguyen 
Here is Power Query solution which you can paste in advanced editor, just use your source.
Or take step by step.

Here are steps:
1) in power query select column State > Transform > unpivot other columns

2) rename collumn "Attribute" to "Product"

3) go to Transform > Group By and add these settings:

4) Last, filter out if value = 0

Result:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYgOlWJ1oJScwS0fJCMxDlXOEyhkqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [State = _t, #"Product 1" = _t, #"Product 2" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"State", type text}, {"Product 1", Int64.Type}, {"Product 2", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"State"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Product"}}),
#"Grouped Rows" = Table.Group(#"Renamed Columns", {"State", "Product"}, {{"Quantity", each List.Sum([Value]), type number}}),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Quantity] <> 0))
in
#"Filtered Rows"

Cheers,
Nemanja Andic