Forum Discussion
Writing Power Query (M language) functions that tailor table scope based on current row values
I have loaded a simple flat file I have pulled into a query. Fairly early on in the query's Applied Steps, the data looks like this:
.
Note that the rightmost column is just a hand-typed set of values that I am HOPING I can achieve with M code, with your help.
Here is the code for the full query:
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 Column" = Table.AddColumn(#"Changed Type", "Candidate Score Evaluation", each if [Candidate Score] = List.Min(Table.SelectRows(#"Changed Type",
each ([Specific Identifier] = [Specific Identifier] and [Qualifier] <> "Ignore"))[Candidate Score])
then "Lowest"
else null)
in
#"Added Conditional Column"You will notice that the column added in the final step is supposed to, for each row, (a) focus only on rows in the table that share the same value in the "Specific Identifier" column as the current row and (b) ignore rows that have the word 'Ignore' in the "Qualifier" column. It does not achieve the desired results.
Please help.
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 :)
6 Replies
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 :)
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!