CODEWITHSUNDEEP
powerbidaxpowerquerypowerbi-desktopm
techturtle
techturtle

Reputation: 2587

Removing multiple predefined strings in a Power BI table so only the free-form text remains

I'm working with a table of data that comes from a Microsoft Form. Some of the questions allowed multiple answers, which are all saved as semicolon-separated values within a single column. Assuming a question has options for Red, Green, Blue and Other (free text), a sample might look like this:

ID  Colors
1   Red;
2   Green;yellow-orange;
3   Red;Blue;
4   purple;
5   Red;gold;

So far I've been able to work with the predefined answers using added columns created like this:

Red = IF(CONTAINSSTRING([Colors],"Red;"),1,0)

This allows me to sum, count, etc. each of these answers. However, I'm struggling to figure out how to best handle the free-form text from the "Other" category. Right now, I have come up with the following formula to output only the Other category:

Other Text = SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Colors],"Red;",""),"Green;",""),"Blue;","")

This works to output the Other text by itself, but the formula is already unwieldy with only a few predefined answers. The possibility of needing to do this for 10+ answers (which I anticipate happening) is daunting. Is there a more direct or succinct way to achieve the same output?

Upvotes: 0

Views: 36

Answers (1)

davidebacci
davidebacci

Reputation: 30219

This is a data restructuring problem. You need to reshape your data in Power Query. e.g.

enter image description here

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpKTbFWitWJVjICctyLUlPzrCtTc3Lyy3XzixLz0lMhksZQlU45pVARE6BIQWlRQQ6UbwpVkZ6fAzIwFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Colors = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Colors", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Colors", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Colors"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Colors", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each [Colors] <> null and [Colors] <> "")
in
    #"Filtered Rows"

enter image description here

Now you can easily count colours or others as follows:

enter image description here

enter image description here

Upvotes: 0

Related Questions