CSV values to an individual item in a drop down list

[Anonymous]

My current drop down list is something like below, and how can I have convert CSV values to an individual item in a drop down list?

Sample data:

ID	Promotion	Category	Content Type	Email Goal	Countries	SEND DATE
WR762443	No	Consumables	Awareness	Open Rate	AA, KR, TW	Monday, January 20, 2020
WR862444	Yes	Consumables	Webinar	Open Rate	SA, SG, KR, TW	Thursday, February 20, 2020
WR563248	No	Consumables	Video	Click Thru Rate	AA	Monday, January 20, 2020
WR363249	No	Consumables	eCom	Click Thru Rate	AA, SA, SG, KR, TW	Monday, February 17, 2020
WR764639	No	Consumables	Promo	Click Thru Rate	SG, TW	Tuesday, January 14, 2020
WR864640	No	Consumables	Awareness	Click Thru Rate	AA, SA	Tuesday, January 28, 2020

[dax · Accepted solution]

Hi @Anonymous , 

As mentioned above, you could use convert this in power query. You could refer to my sample for details.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZFdC8IgFIb/iuzai+XMrcsICoo+WKMR0YWrA42ahibRv08XfWw4ulA8B3keX89uF+RpzAilUYCDhbTbSAptKl5cQNtqeOcKBGh3Xl5BoJTfwPWHGM1SjLLcFnMpjvyB0ZQLw9UDkRDbRcJgjx0/cXxq721rZFOQQ1EKrlr4tcWvJz+K7GSUriVjKJTP0mcRoYk/xaY8Qt29lIczyk7KfHP8f3/kyAM/GUay6gDbBO0Ub9EnQy9umGJGWdRhWilZ+TPUjtcvGdCNJD3amgRlNPw/6a44PgVJ3or9Ew==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Promotion = _t, Category = _t, #"Content Type" = _t, #"Email Goal" = _t, Countries = _t, #"SEND DATE" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Promotion", type text}, {"Category", type text}, {"Content Type", type text}, {"Email Goal", type text}, {"Countries", type text}, {"SEND DATE", type date}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Countries", "Countries - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Countries - Copy", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Countries - Copy.1", "Countries - Copy.2", "Countries - Copy.3", "Countries - Copy.4", "Countries - Copy.5"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"ID", "Promotion", "Category", "Content Type", "Email Goal", "Countries", "SEND DATE"}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
    #"Removed Columns1"

Best Regards,
Zoe Zhi

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

[speedramps]

Hi lostinspace

Please consider this solution and leave kudos ...

In the power query editor click on your countries column > split by column > split by delimniter

Click advanced>rows>ok

This will create one row per id / country.

You can use this instead of your orginal table or as a 1 to many relationship by id. 

Add the country column to your drop down and this will select just what you want.

Alternatively, you can use search string options but the above method will be a lot faster.

[mahoneypat]

You can do that by adding a custom column with Text.Split([Countries], ", "), and click on the expand arrows of the list that is created and choose "Expand to New Rows".  This will duplicate all the other columns but create a new row for each of the Countries.  This may affect other existing measures, so you may consider referencing your original table in the query editor, keeping a key column to relate them later and your countries column, and perform this transformaton.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Pragati11]

Hi @Anonymous ,

You can achieve this Query Editor in Power BI:

1. Split Country column on "," separator
2. Select all the splitted columns and Unpivot selected columns.

You should end up what you are looking for. Let me know if you need more details on this.

Thanks,

Pragati