Forum Discussion

Proggesnekka_v2's avatar
Proggesnekka_v2
Regular Visitor
3 years ago

Multiple dummy variables to one column

I have 3 dummy variables, D1-D3. Where I calculate the number of Customers with the measure “No. measure”.     However, I want to create a variable “Category”, to calculate the No.measure,  ...
mahenkj2's avatar
mahenkj2
Icon for Solution Sage rankSolution Sage
3 years ago

Hi Proggesnekka_v2 

 

One of the way might be in Power query, not sure how is your table and other data but for the sample provided by you, it should work:

 

Sample data used:

D1 D2 D3 No. measure
0 1 0 11492
1 0 0 6699
1 1 0 3272
0 0 1 1324
0 1 1 353
1 0 1 1345
1 1 1 698

 

 

Just past above code in PQ and it should format the table in such way that then you can simply produce the result as below:

Hope it helps.

  • mahenkj2's avatar
    mahenkj2
    Icon for Solution Sage rankSolution Sage
    3 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTIEYjBtaGJppBSrEw0XAWEzM0tLuCBMwtjIHKISpgosaWxkAheEqTY2NUYxEqLQxBTFSBA2s7RQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [D1 = _t, D2 = _t, D3 = _t, #"No. measure" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"D1", Int64.Type}, {"D2", Int64.Type}, {"D3", Int64.Type}, {"No. measure", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"No. measure"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Category"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Value] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value"})
in
    #"Removed Columns"