Forum Discussion
Group Columns using Power Query with Multiple Conditions
I have been converting all of my companies reports from an online system Chartio, which is closing shop in March of 2022, and one of the nice things about that system is you could do Groupings on Columns by MIN, MAX, COUNT DISTINCT, and of course GROUPING (see screenshot below). I know how to do basic Group By using Power Query but how would you go about doing all of these at once in the Group By function? Any advice or push in the right direction would be greatly appreciated.
Here is a screenshot from the Chartio report writer:
Here is the sample of my data in Power Query:
Hi, MP-iCONN
You can modify the count part in your original code.
Like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvc3sjQwNFLSUTIyMLTUNTDTNbIEciA4VgerAgsgxxSMsSkw1zUAcQwNIAQOJcYgWUMIgaokrzQnByQOxjh0g1wAFlCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"WO ID" = _t, #"Day of WKE Lab Start Time" = _t, #"Avg Emp Count" = _t, #"Max Emp Count" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"WO ID", type text}, {"Day of WKE Lab Start Time", type date}, {"Avg Emp Count", Int64.Type}, {"Max Emp Count", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"WO ID"}, {{"Avg", each List.Average([Avg Emp Count]), type nullable number}, {"Max", each List.Max([Max Emp Count]), type nullable number}, {"Count", each Table.RowCount(Table.SelectRows(_,(x)=>x[Day of WKE Lab Start Time]<>null)), Int64.Type}}) in #"Grouped Rows"Table.SelectRows(_,(x)=>x[Day of WKE Lab Start Time]<>null)Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,
Community Support Team _ Janey
4 Replies
smpa01 sure. Attached are two csv files. The one called Sample_Data_Before_Grouping.csv is before and grouping's are taken place. The second file, Sample_Data_After_Grouping.csv is after those groupings are taken place from my Chartio screenshot.
Before:
After:
Thank you for your help on this!
Actually to make it easier I think I found a way to do this but now I am only stuck on this. How to get a count of "Day of WKE Lab Start Time" and not include the null.
I took this data:
WO ID Day of WKE Lab Start Time Avg Emp Count Max Emp Count WO29012 2019-06-29 9 9 WO29012 2019-06-28 5 5 WO29012 2019-07-02 10 10 WO29012 2019-07-03 11 11 WO29012 null 1 1 WO29012 2019-07-08 2 2
Then grouped like this to get the count (which I am not sure I am doing right with the count part):Then got this. I ultimatly should have a count of 5 for the "Day of WKE Lab Start Time"
WO ID Avg Max Count WO29012 6.33333333333333 11 6 Hi, MP-iCONN
You can modify the count part in your original code.
Like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvc3sjQwNFLSUTIyMLTUNTDTNbIEciA4VgerAgsgxxSMsSkw1zUAcQwNIAQOJcYgWUMIgaokrzQnByQOxjh0g1wAFlCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"WO ID" = _t, #"Day of WKE Lab Start Time" = _t, #"Avg Emp Count" = _t, #"Max Emp Count" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"WO ID", type text}, {"Day of WKE Lab Start Time", type date}, {"Avg Emp Count", Int64.Type}, {"Max Emp Count", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"WO ID"}, {{"Avg", each List.Average([Avg Emp Count]), type nullable number}, {"Max", each List.Max([Max Emp Count]), type nullable number}, {"Count", each Table.RowCount(Table.SelectRows(_,(x)=>x[Day of WKE Lab Start Time]<>null)), Int64.Type}}) in #"Grouped Rows"Table.SelectRows(_,(x)=>x[Day of WKE Lab Start Time]<>null)Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,
Community Support Team _ Janey
