Forum Discussion
Writing Power Query (M language) functions that tailor table scope based on current row values
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"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 :)
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 :)
Thanks for that. I will look for opportunities to deploy that.
Going back to my original code, which I adapted using your logic, Chris Webb was kind enough to point out to me offline that it would work better with a larger data set (which is defintely the motivation behind all of these questions) if the main table were buffered. It now looks like this:
let
Source = Csv.Document(File.Contents("H:\Misc\Power Query experiment\Data2.csv"),[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Broad Identifier", Int64.Type}, {"Specific Identifier", type number}, {"Candidate", type number}, {"Candidate Score", type number}, {"Qualifier", type text}, {"Candidate Score Evaluation, Desired Outcome", type text}}),
#"Added Conditional Column1" = Table.AddColumn(#"Changed Type", "Qualified Candidate Score", each if [Qualifier] <> "Ignore" then [Candidate Score] else null),
#"Reordered Columns" = Table.ReorderColumns(#"Added Conditional Column1",{"Broad Identifier", "Specific Identifier", "Candidate", "Candidate Score", "Qualifier", "Qualified Candidate Score", "Candidate Score Evaluation, Desired Outcome"}),
#"Buffered Table" = Table.Buffer(#"Reordered Columns"),
#"Added Conditional Column" = Table.AddColumn(#"Buffered Table", "Candidate Score Evaluation", each if (let
group = [Specific Identifier]
in
List.Min(Table.SelectRows(#"Buffered Table", each [Specific Identifier] = group)[Qualified Candidate Score])) = [Candidate Score]
then "Lowest"
else null),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Candidate Score Evaluation", type text}})
in
#"Changed Type1"Cheers
That's a very good point, thank you for sharing!