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.
Guys,
i have annoying task to do every month and thought maybe there is some sollution. On a screen below you will see different values that should be counted. I need to count how many times the same value is repeated. It seems nothing complicated, but in some cases there are several values in one field separated by commas, f.e. in a 3rd row we have 3 values 8.15, 6.56 and 3.18, which must be counted as separate values. Value 3.18 repeats 3 times in total, so final result should be 3.18 - 3
Below is final, expected result:
Any suggestion how to count each comma separated value as a separate value? 😉
PS.: the word str. will be removed via column transformation
Solved! Go to Solution.
@Daniel_F
Step1: First Seperate the value by comma seperated as per below
result was:
Step2:Select all 3 column and unpivot selected column as per below
then remove unnecessary column and you can replace str. with blank
now create visual table as per below
Thx for all 🙂 this is what i was looking for 🙂
Hey @Daniel_F ,
in a situation like this, I create a new table that contains two columns STR and Row Labels. STR Still contains the comma separated values, whereas Row Labels has the string separated into multiple rows, then you can create a relationship with the original table and write a measure that counts the number of occurrences of a given string now, a Row Label.
The table will look like this:
In PowerQuery you start with a table containing a single column of unique values of STR, create a duplicate column of STR.
Then you can use the "Split column - By delimiter transformation on the duplicated column:
Without any measure you can achieve something like this:
Hopefully, this provides what you are looking for.
Regards,
Tom
@Daniel_F
Step1: First Seperate the value by comma seperated as per below
result was:
Step2:Select all 3 column and unpivot selected column as per below
then remove unnecessary column and you can replace str. with blank
now create visual table as per below
Yes, this is what I need, but now another question has appeared. After i split my column and before unpivot i have to select all newly appeared columns and then select Unpivot selected columns, but what if i'll have more than 3 values separated by comma, f.e. 4-5-6? Then applied steps skip those new 4-5-6 columns. So my question is - is there any function that dynamically unpivot all new appeared columns after split? Hope you understand what I mean 🙂
In Applied steps i see this function:
Table.UnpivotOtherColumns(#"Split Column by Delimiter", {}, "Attribute", "Value")
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |