cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AlexPBIF Frequent Visitor
Frequent Visitor

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:

 

Annotation 2019-05-16 095319.png

 

 

 

.

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.

 

2 ACCEPTED SOLUTIONS

Accepted Solutions

Re: 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"

Scores.PNG

Re: Writing Power Query (M language) functions that tailor table scope based on current row values

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.: 

 

GroupValue
A1
A2
A3
B4
B5
B6
A7
A8
C9
C10

 

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)
GroupGroup Sum
A6
B15
A15
C19

 

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 Smiley Happy

6 REPLIES 6

Re: Writing Power Query (M language) functions that tailor table scope based on current row values

Hi @AlexPBIF,

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!

Re: 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"

Scores.PNG

AlexPBIF Frequent Visitor
Frequent Visitor

Re: Writing Power Query (M language) functions that tailor table scope based on current row values

You are solid gold, my fried. That worked!

 

At some point, I'm also going to take your first solution out for a spin.

 

Thank you very much.

Re: Writing Power Query (M language) functions that tailor table scope based on current row values

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.: 

 

GroupValue
A1
A2
A3
B4
B5
B6
A7
A8
C9
C10

 

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)
GroupGroup Sum
A6
B15
A15
C19

 

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 Smiley Happy

AlexPBIF Frequent Visitor
Frequent Visitor

Re: Writing Power Query (M language) functions that tailor table scope based on current row values

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

 

Re: Writing Power Query (M language) functions that tailor table scope based on current row values

That's a very good point, thank you for sharing!

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 93 members 1,549 guests
Please welcome our newest community members: