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.
In my query editor, I have these columns. The column names differ, but the values are all the same:
I'm trying to find a way to get the counts of the values without unpivoting. I've already unpivoted this data before which makes everything easy to count, but it does not allow me to calculate averages over time and things of that nature correctly once it is unpivoted. How else could I get a count of each value?
Solved! Go to Solution.
HI @Anonymous,
You can refer to below formula to transform table as group result.
Power query custom function:
let Groupping= (source as table) => let Source= Text.Split(Text.Combine(Table.ToList(source),","),","), Transformed=Table.FromList(List.Distinct(Source),Splitter.SplitByNothing(),{"Item"}), Output = Table.AddColumn(Transformed,"Count", each Function.Invoke((x)=> List.Count(List.Select(Source, each _ = x)),{_[Item]})) in Output in Groupping
Notice: my function not works for value with date format, please convert date type as text before invoke.
Regards,
Xiaoxin Sheng
HI @Anonymous,
You can refer to below formula to transform table as group result.
Power query custom function:
let Groupping= (source as table) => let Source= Text.Split(Text.Combine(Table.ToList(source),","),","), Transformed=Table.FromList(List.Distinct(Source),Splitter.SplitByNothing(),{"Item"}), Output = Table.AddColumn(Transformed,"Count", each Function.Invoke((x)=> List.Count(List.Select(Source, each _ = x)),{_[Item]})) in Output in Groupping
Notice: my function not works for value with date format, please convert date type as text before invoke.
Regards,
Xiaoxin Sheng
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |