Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Here is the similar kind of table I would be using it in the powerbi. I wanted to show a table visual that show the ID and for that particular ID if the category is present we need to show Y ( yes ) if not then N (No).
I wanted show it in this way in powerbi table visual
Solved! Go to Solution.
Hi @User1999
I have loaded the table
Please Copy this code in Advance Editor
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Value]), "Value", "Attribute", List.Count),
Custom1 = Table.TransformColumns(#"Pivoted Column",
{
{
"snacks",each
if _ = 1 then "Y"
else "N"
},
{
"biscuits",each
if _ = 1 then "Y"
else "N"
},
{
"beverages",each
if _ = 1 then "Y"
else "N"
},
{
"chocolates",each
if _ = 1 then "Y"
else "N"
}
}
)
in
Custom1
FULL CODE:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyMlTSUSrOS0zOLlaK1YGLJGUWJ5dmlqCKpZalFiWmp6IIJmfkJ+fnJJbARI2NDXCKohoKFUM11MTEBMk9sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Category = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"ID", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Value]), "Value", "Attribute", List.Count),
Custom1 = Table.TransformColumns(#"Pivoted Column",
{
{
"snacks",each
if _ = 1 then "Y"
else "N"
},
{
"biscuits",each
if _ = 1 then "Y"
else "N"
},
{
"beverages",each
if _ = 1 then "Y"
else "N"
},
{
"chocolates",each
if _ = 1 then "Y"
else "N"
}
}
)
in
Custom1
Finally The Power Query gets transformed as below:
Pull the required in Table Visual as below:
If you don't want to get into trnasposing and pivoting the table in Power Query, I would just create a few measures. There will need to be one measure for each category, but they are all very simple to setup.
Each measure will follow this format:
Snacks = IF( 'Table1'[Category] = "Snacks", TRUE(), FALSE())
Repeat this for all categories. Then you can put the 'Table1'[ID] column into a table or matrix and place your meaures along with it.
Hi @User1999
I have loaded the table
Please Copy this code in Advance Editor
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Value]), "Value", "Attribute", List.Count),
Custom1 = Table.TransformColumns(#"Pivoted Column",
{
{
"snacks",each
if _ = 1 then "Y"
else "N"
},
{
"biscuits",each
if _ = 1 then "Y"
else "N"
},
{
"beverages",each
if _ = 1 then "Y"
else "N"
},
{
"chocolates",each
if _ = 1 then "Y"
else "N"
}
}
)
in
Custom1
FULL CODE:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyMlTSUSrOS0zOLlaK1YGLJGUWJ5dmlqCKpZalFiWmp6IIJmfkJ+fnJJbARI2NDXCKohoKFUM11MTEBMk9sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Category = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"ID", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Value]), "Value", "Attribute", List.Count),
Custom1 = Table.TransformColumns(#"Pivoted Column",
{
{
"snacks",each
if _ = 1 then "Y"
else "N"
},
{
"biscuits",each
if _ = 1 then "Y"
else "N"
},
{
"beverages",each
if _ = 1 then "Y"
else "N"
},
{
"chocolates",each
if _ = 1 then "Y"
else "N"
}
}
)
in
Custom1
Finally The Power Query gets transformed as below:
Pull the required in Table Visual as below:
You can try to use the matrix with this measure:
User | Count |
---|---|
93 | |
86 | |
78 | |
72 | |
66 |
User | Count |
---|---|
115 | |
106 | |
85 | |
65 | |
64 |