Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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")
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |