Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
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.
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?
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.
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!!
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.
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.
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?
Hi @SusuYes ,
Not equal to in power query is represented as <>,
So you can use this in if condition
@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/
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.
@SusuYes , first replace " & "by " , " and then try
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |