Advice/suggestions on data modelling a column with multiple selections

[SusuYes]

Hello everyone, 

I have a form where for employers to register on a website. Part of the form is a checkbox/dropdown menu of the multiple industries and employers can choose one or more industry from a preselected list that relevant to their company. 

When I export this data, this information is exported into a single coloumn where each selection is separated by a comma. My problem is that some of the preselected industiries have a comma in their name e.g. "Accounting Services, Administration & Office Support". This means that I cannot easily seprate them into different coloumns. See pic to get an idea about my data. 

I want to be able to report on how many employers have registered from each industry. The query/measure should investigate every row of the 'Industry' coloumn and count the number of industries' selected. I have a list of all the indsutries which I have attached here.  

Does anyone have any ideas or suggestions of how I can go about this? 

[v-rzhou-msft · Accepted solution]

Hi @SusuYes 

Here I suggest you to replace delimeter in values like 'Accounting Services, Administration & Office Support' to symbol which we couldn't find in [Industry] column like ".". Then you can split other values by delimeter. Finally, you can replace "." in 'Accounting Services. Administration & Office Support' by "," or ";" to get result you want.

My Sample.

Create a custom column as below.

let 
_A = "Accounting Services, Administration & Office Support",
_NewA = "Accounting Services. Administration & Office Support"
in
if 
Text.Contains([Industry], _A) 
then 
Replacer.ReplaceText([Industry],_A,_NewA) 
else [Industry]

Do steps as above and result is as below.

Whole M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ogpNTAwMvNVitWJVnJMTs4vzSvJzEtXCE4tKstMTi3WUXBMyc3MyywuKUosyczPU4CoV/BPSwNKKwSXFhTkF5WAVJWlFoEN8U0syizJzE0Fc1whykMgxqen6iAEYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Industry = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Industry", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom.1", each let 
_A = "Accounting Services, Administration & Office Support",
_NewA = "Accounting Services. Administration & Office Support"
in
if 
Text.Contains([Industry], _A) 
then 
Replacer.ReplaceText([Industry],_A,_NewA) 
else [Industry]),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1.1", "Custom.1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1.1", type text}, {"Custom.1.2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",".",",",Replacer.ReplaceText,{"Custom.1.1"})
in
    #"Replaced Value"

Best Regards,
Rico Zhou

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

[SusuYes]

Hi Rico, 

does this mean that I will need to write variables for every 'Industry' entry that I need to split? 

For example, you can see in 'Industry' coloumn that there are multiple and repeatable items.

 Does this mean I will need to introduce a new variable in the code similar to _A and _NewA? 

[v-rzhou-msft]

Hi @SusuYes 

If you have multiple entries like the screenshot, you may need to tell Power BI which it need to keep when transform. So you need to use _B,_NewB in your code. From your screenshot, I see all entries you want to keep "," are started by

"Accounting Services", maybe you can let _A = "Accounting Services, "_New_A = "Accounting Services. "

Best Regards,
Rico Zhou

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

[Anonymous]

Hello @SusuYes 
Is it possible to change delimeter to semicolon in preselected industries name?
For eg.:- Advertising , Arts & Media -> Advertising ; Arts & Media
If this is possible then in power query you can split column by delimeter ',' and preselected industries will not be affected.

Did I answer your question?
Mark my post as a solution!
Appreciate your Kudos!!

[SusuYes]

Hi Muskan, I have thought about that but unfortuntaly the system that I am using does not allow you to change the delimeter. Moving forward, I have fixed the preselected industris to avoid this but I still need to report on this years data. 

[Anonymous]

Hi @SusuYes , then you can add custom column with if condition where you can check that industry name !=  Advertising , Arts & Media(all industries with delimeter that we don't want to split) then split the column else nothing to be done.

[SusuYes]

This sounds like an interesting appraoch, I have not used != before, can you please expand a little bit? Would I need to write a measure to achieve that? 

[Anonymous]

Hi @SusuYes ,
Not equal to in power query is represented as <>,
So you can use this in if condition

[amitchandak]

@SusuYes , one of the option you have is split by dimlimter into rows in power query

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

[SusuYes]

Thanks for the comment. The only problem I have with this option is that some entries have a delimeter in them. For example, 'Accounting Services, Administration & Office Support' is one entry and should not be split. 

[amitchandak]

@SusuYes , first replace " & "by " , " and then try