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 all, I have searched the forum for a solution to this but haven't managed to find what I need.
I have the following dataset and need to count the occurrences of each two letter code in columns [Spot 1] to [Spot 10] for each row. The result I'm hoping for is shown in the final 5 columns, as highlighted in bold.
Site | Category | Spot 1 | Spot 2 | Spot 3 | Spot 4 | Spot 5 | Spot 6 | Spot 7 | Spot 8 | Spot 9 | Spot 10 | NO | EA | CL | RS | SC |
A | A | NO | NO | NO | NO | NO | NO | NO | NO | NO | NO | 10 | null | null | null | null |
A | B | EA | EA | EA | CL | EA | EA | EA | EA | CL | EA | null | 8 | 2 | null | null |
B | A | SC | NO | NO | NO | NO | NO | NO | NO | NO | NO | 9 | null | null | null | 1 |
B | B | RS | RS | RS | RS | CL | CL | RS | RS | RS | RS,CL | null | null | 3 | 8 | null |
It is complicated by there being some occurrences of two codes, separated by a comma, as you can see in the last row for [Spot 10].
I'd prefer to do this in the Query Editor. Any help would be greatly appreciated!
Solved! Go to Solution.
In that case
=List.Count( List.Select( Text.Split( Text.Combine( List.Transform(Record.ToList(_),each Text.From(_)) ,",") , "," ),each _ ="CL") )
Hi @EugenioJunior, yes I could do that. So in the example, [Spot 10] is transformed into two columns; [Spot 10.1] and [Spot 10.2]
So it worked?
Just another way could be yo use this Custom Column
For example for CL
=List.Count( List.Select( Text.Split( Text.Combine(Record.ToList(_),","), "," ),each _ ="CL"))
Thank you @Zubair_Muhammad, that works nicely. Only problem is that, unlike in my example, I have a couple of numeric columns in my table, which lead to an error. Is there a way to ammend your code so that it looks at the [Spot] columns only?
Thanks @EugenioJunior, the splitting advice helps, but it is the code to create the final five columns in my example that I really needed.
In that case
=List.Count( List.Select( Text.Split( Text.Combine( List.Transform(Record.ToList(_),each Text.From(_)) ,",") , "," ),each _ ="CL") )
Thanks @Zubair_Muhammad, it is giving the following error though:
Expression.Error: The name '_' wasn't recognized. Make sure it's spelled correctly.
Hi @tgjones43
Please see the attached file's Query Editor for the custom column.
Let me know if it helps
Thanks @Zubair_Muhammad for the file but I cannot open it, I get a message saying 'it's incompatiable with your current version of Power BI' (my company is using October 2018).
Is there anything else we could try?
Sure, here it is
You need to place the formula, I provided above in a new custom column as shown in pic below
@Zubair_Muhammadthat has done it, thank you so much.
Why does the formula need to be presented like that, rather than on one line like I had it?
Hi @tgjones43
The formula bar shows the Complete M Code for the step.
The Custom Column just shows the formula part
You can also view the entire code for the Query from View Tab>>Advanced Editor.
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |