Forum Discussion
Dummy variable problem
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
2 Replies
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.
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"
