Forum Discussion
power query average using multiple criteria and grouping
Thank you Gao. As I am so new to this, the above code is a lot for me to understand. I tried to copy it and add it to a custom column but that did not work.
Am I right in thinking that I can add a custom column into my PQ table and add in the middle part of that code which would look like this (named the custom column "New Hire Avg CR")?
if [New Hire] = "New Hire" and [#"Compa Ratio"] <> "" and Number.From([#"Compa Ratio"]) > 0 then
List.Average(Table.SelectRows[#"Job Code & Country"]=[#"Job Code & Country"] and [New Hire]="New Hire") [#"Compa Ratio"] else null
Then the last part of your code would be formatting this custom column to percentage?
I have tried it as I described above, and although the formula looked ok in the editor, the average for New Hire is not calculating and is showing as error (the Existing employees are showing a null value which is correct). Could you please advise further on this?
Super Userlet
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
- lbendlin
That code represents a compressed version of the sample data. Throw it away when you swap in your actual data source.