Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
@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"
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.
User | Count |
---|---|
59 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
85 | |
54 | |
45 | |
39 | |
21 |