Counting Across Columns?

[Anonymous]

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?

[v-shex-msft · Accepted 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

[Anonymous]

@v-shex-msft thank you! This is a bit over my head but I will give it a try.