How to show yes or no for a category column

[User1999]

 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 

[Chakravarthy · Accepted 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:

[Ahmedx · Accepted solution]

pls try this

[d_rohlfs]

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.

[Chakravarthy · Accepted 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:

[jmarciogsousa]

You can try to use the matrix with this measure: