Forum Discussion
power query average using multiple criteria and grouping
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZVBa8JAEIX/igR687Azk012zyXUttBbT+JBMJRAjWADtv++1iQ6rq9Uh57CJrzHm33fbubzrKocZdPs6d4dnq9t09WryXPTvq026/HDxVuScLd/VJ/NR7d/mS2mByc2OBH9OL3Uu8ms2dajkyinx275/jWuxwVxjnS5IQELmsUrp4d6u162xwynJXFE2sKQwgc0T2lwciXKFAYBX8zDah5ipI1Kq9vgYxveAx05pYPpGfAAncjg1HeT7CixJRMihOTfnAZq/W8te3QCC+TkldNZy15TS4haKq7RRnTsqDQM4GCIYHDq4UszRYNTgeDjAWNJ8JcT/kJIR0qHEggARNA1wGxwYocyiXI6K1nUNRByJM1vDxEETeP/3E+HKOPi9gARnRIuDUboYuRg2BJYcLymlj5DIhVnGAZhLgZcI7rORNOK+4U/fRFDAAS5GEgll2zu4hs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, #"Job Code" = _t, Country = _t, #"Job Code & Country" = _t, #"Compa Ratio" = _t, #"New Hire" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Job Code", type text}, {"Country", type text}, {"Job Code & Country", type text}, {"Compa Ratio", Percentage.Type}, {"New Hire", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "New Hire Avg CR", (k)=> if k[New Hire]="New Hire" then List.Average(Table.SelectRows(#"Changed Type", each [New Hire]="New Hire" and [Compa Ratio]<>null and k[#"Job Code & Country"]=[#"Job Code & Country"])[Compa Ratio]) else null, Percentage.Type)
in
#"Added Custom"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
Thank you Ibendlin. Gao and you both gave me this code and I just wanted to understand it in case I need to edit or create a similar calculated column again. Is this begining part of the code always the same or does it differ depending on the query/table that I am in? (I am proficient in Excel, but very new new to the data model and DAX)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZVBa8JAEIX/igR687Azk012zyXUttBbT+JBMJRAjWADtv++1iQ6rq9Uh57CJrzHm33fbubzrKocZdPs6d4dnq9t09WryXPTvq026/HDxVuScLd/VJ/NR7d/mS2mByc2OBH9OL3Uu8ms2dajkyinx275/jWuxwVxjnS5IQELmsUrp4d6u162xwynJXFE2sKQwgc0T2lwciXKFAYBX8zDah5ipI1Kq9vgYxveAx05pYPpGfAAncjg1HeT7CixJRMihOTfnAZq/W8te3QCC+TkldNZy15TS4haKq7RRnTsqDQM4GCIYHDq4UszRYNTgeDjAWNJ8JcT/kJIR0qHEggARNA1wGxwYocyiXI6K1nUNRByJM1vDxEETeP/3E+HKOPi9gARnRIuDUboYuRg2BJYcLymlj5DIhVnGAZhLgZcI7rORNOK+4U/fRFDAAS5GEgll2zu4hs=", BinaryEncoding.Base64)
This is why I was looking to try and add a formula as a custom column so I can at least try to understand it when I have to edit it
That code represents a compressed version of the sample data. Throw it away when you swap in your actual data source.