Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
michaelu1
Helper III
Helper III

Changing 1/0 to Yes/No for a slicer

I have many columns of data stored in 1/0 that I need to create slicers for.

 

I could replace the 1/0 to Yes/No, but, if I understand correctly, data is stored much better in PBI as an integer.

 

Is there an easy to swap the 1/0 for a Yes/No with adding data to the model?

4 REPLIES 4
vicky_
Super User
Super User

One easy way of doing this is to just create a new table, with TableName = {(1, "Yes"), (0, "No")}, create a relationship and use the new table in the slicer. I'm not sure there's a way to change the display value without at least creating a new column or table. 

So essentially I'll need to create a ton of little tables for each of my slicers..

 

Not the worst thing, but does clutter up the model.

Hi @michaelu1 ,

 

You can use Replace Values in Power Query:

vjianbolimsft_0-1686721959799.png

Then change the data type:

vjianbolimsft_1-1686722028687.png

Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWJVnICsgzALGc4ywUiGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Column1 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"1","True",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","0","False",Replacer.ReplaceText,{"Column1"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"Column1", type logical}})
in
    #"Changed Type"

Final output:

vjianbolimsft_2-1686722074403.png

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-jianboli-msft 

 

My understanding is that storing data as integers takes up the least amount of space.

 

Once I'm changing the data to something else I can just switch it to Yes/No. My question was how can I keep it as 1/0 in the model and still have the slicer showing Yes/No.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.