Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |