Forum Discussion
Counting Across Columns?
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?
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 GrouppingNotice: my function not works for value with date format, please convert date type as text before invoke.
Regards,
Xiaoxin Sheng
2 Replies
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 GrouppingNotice: my function not works for value with date format, please convert date type as text before invoke.
Regards,
Xiaoxin Sheng