Dummy variable problem

[Cognos]

Hello everyone, 

need some help, have this table:

	X2	DUMMY_1	DUMMY_2	DUMMY_3	X4	X5
ID	12334	1	1	0	1234	45566
ID2	1233	1	0	1	125	123
ID4	564	0	0	1	123	1231
ID5	67878	1	0	1	13	1222

And I want to make a table  like this

	SUM
DUMMY_1	3
DUMMY_3	1
DUMMY_3	3

Let me know if you need further information.

Thanks in advance

[AntrikshSharma]

@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]

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.

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

Thanks.