Forum Discussion
Writing Power Query (M language) functions that tailor table scope based on current row values
- 7 years ago
I re-read this and have another idea that may be easier. I created a helper column to determine which scores should be evaluated:
let Source = Excel.CurrentWorkbook(){[Name="Scores"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Specific Identifier", type text}, {"Score", Int64.Type}, {"Qualifier", type text}}), #"Added Qualified Scores" = Table.AddColumn(#"Changed Type", "Qualified Scores", each if [Qualifier] <> "Ignore" then [Score] else null), #"Added Outcome" = Table.AddColumn(#"Added Qualified Scores", "Outcome", each if (let group = [Specific Identifier] in List.Min(Table.SelectRows(#"Added Qualified Scores", each [Specific Identifier] = group) [Qualified Scores])) = [Score] then "Lowest" else "") in #"Added Outcome" - 7 years ago
Happy to help!
As an aside, GroupKind.Local can be a powerful tool if you're grouping rows based on proximity to one another - especially if values are repeated later on but should be kept separate i.e.:
Group Value A 1 A 2 A 3 B 4 B 5 B 6 A 7 A 8 C 9 C 10 In a table like this, if the two A groups ({1,2,3} and {7,8}) should be aggregated separately, GroupKind.Local would allow you to do that.
= Table.Group(#"Source Table", {"Group"}, {"Group Sum", each List.Sum([Value])}, GroupKind.Local)Group Group Sum A 6 B 15 A 15 C 19 When I re-read your post, I realized GroupKind.Local would have worked but wasn't necessary... but it's still a good tool to know about and one that someone just recently showed me so now I'm just trying to share the wealth :)
Hi Anonymous,
Have you tried using Table.Group with the 4th parameter set to GroupKind.Local?
This will allow you to perform functions (i.e. List.Min()) on each group of Specific Indentifiers and could be nested with an if statement to make sure Qualifier <> "Ignore."
Hope this helps!