Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi there,
I'm still realitvely new to PowerBI and I'm having trouble counting the number of choices selected for a program.
The table currently looks like this:
Program 1 | Choice 1; Choice 2; Choice 3
Program 2 | Choice 1; Choice 2
Program 3 | Choice 1
What I want on the report is a table that looks like this:
Program 1 | 3
Program 2 | 2
Program 3 | 1
I've been unable to figure out how, it would be a great help if someone can offer a solution.
In a custom column, put following where Column2 should be replaced appropriately
List.Count(Text.Split([Column2], ";"))
Test code here
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTy9KzFUwVFDSUVJwzsjPTE5VMLSGsYzgLGOlWB2EciMcylEUGaMoUoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
Custom2 = Table.AddColumn(Source, "Count", each List.Count(Text.Split([Column2], ";")), Int64.Type)
in
Custom2
Ok, what happens if I have some missing values on Column 1 and I want it to appear as 0 on the custom column?
So like from this:
Program 1 | Choice 1; Choice 2; Choice 3
Program 2 |
Program 3 | Choice 1
To this:
Program 1 | 3
Program 2 | 0
Program 3 | 1
Use this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTy9KzFUwVFDSUVJwzsjPTE5VMLSGsYzgLGOlWB2EciMcylEUGaMoQpEyAcooxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
Custom1 = Table.AddColumn(Source, "Count", each if [Column2]="" or [Column2]=null then 0 else List.Count(Text.Split([Column2], ";")), Int64.Type)
in
Custom1
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
21 | |
20 | |
13 | |
13 |