cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Cognos
Regular Visitor

Dummy variable problem

Hello everyone, 

need some help, have this table:

 X2DUMMY_1DUMMY_2DUMMY_3X4X5
ID12334110123445566
ID21233101125123
ID45640011231231
ID567878101131222

 

And I want to make a table  like this

 SUM
DUMMY_13
DUMMY_31
DUMMY_33

 

Let me know if you need further information.

 

Thanks in advance

 

2 REPLIES 2
AntrikshSharma
Resident Rockstar
Resident Rockstar

@Cognos Use Power Query for this kind of transformation:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XYwxDoAgDEWvYjoz2EILB2BxYDTREOL9byGVRoxDf35eX1srLODgoB55L+W88G2TeVWCBkNzFbbcO5L3ytBmHUxRYBYxk0z9Wg/isTBNz1iCGdPyI9E0PZKYYvq/Gx4RtHYD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}, {"(blank).6", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"(blank)", "(blank).2", "(blank).3", "(blank).4"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Other Columns", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{" ", type text}, {"DUMMY_1", Int64.Type}, {"DUMMY_2", Int64.Type}, {"DUMMY_3", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {" "}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{" "}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Attribute"}, {{"Count", each List.Sum([Value]), type number}})
in
    #"Grouped Rows"
Tanushree_Kapse
Solution Supplier
Solution Supplier

Hi @Cognos ,

 

-Transform the table in the Power query:
-Unpivot the three DUMMY columns
-Close and Apply

-Take a table visual
-Pull the new columns in it.

Tanushree_Kapse_0-1632397629806.png

 

Mark this as solution. if I answered your que. Kudos are always appreciated.

Thanks.

 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors
Top Kudoed Authors