Notifications
Clear all
Power Query
2
Posts
2
Users
0
Reactions
649
Views
Topic starter
Hi all,
I am struggling with the following problem: I have a sample data set where I need to calculate the n percentile (say 90% percentile) for records pertaining to a given Area (see screenshot). The percentile of each Area should then be reported in an additional column.
Hope the screenshot clarifies the problem.
Thank you
giovanni
Posted : 18/05/2023 12:50 pm
Hi Giovanni,
Try this query:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Grouped", (x)=> List.Sum(Table.SelectRows(Source, each _[Area] = x[Area])[Custom.Peso])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Percent", each [Custom.Peso]/[Grouped], Percentage.Type)
in
#"Added Custom1"
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Grouped", (x)=> List.Sum(Table.SelectRows(Source, each _[Area] = x[Area])[Custom.Peso])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Percent", each [Custom.Peso]/[Grouped], Percentage.Type)
in
#"Added Custom1"
Posted : 26/05/2023 12:42 am
