Writing Power Query (M language) Table.X functions that will dynamically tailor the table scope to each row of data

Alex MEDC

New Member
Joined
May 16, 2019
Messages
2
I have loaded a simple flat file I have pulled into a query. The data looks like this:


A
B
C
D
E
F
Broad Identifier
Specific Identifier
Candidate
Candidate Score
Qualifier
Candidate Score Evaluation, Desired Outcome
Ignore
Lowest
Lowest
Ignore
Ignore
Lowest
Lowest
Ignore
Ignore

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: center"]2
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]9.333
[/TD]
[TD="align: right"]9.111
[/TD]
[TD="align: right"]0.222
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]9.333
[/TD]
[TD="align: right"]9.111
[/TD]
[TD="align: right"]0.222
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]9.333
[/TD]
[TD="align: right"]9.777
[/TD]
[TD="align: right"]1.444
[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]9.333
[/TD]
[TD="align: right"]9.333
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]9.333
[/TD]
[TD="align: right"]9.555
[/TD]
[TD="align: right"]1.222
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"]14.111
[/TD]
[TD="align: right"]14.222
[/TD]
[TD="align: right"]1.111
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"]14.111
[/TD]
[TD="align: right"]14.111
[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]9
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"]14.111
[/TD]
[TD="align: right"]14.333
[/TD]
[TD="align: right"]1.222
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"]14.111
[/TD]
[TD="align: right"]14.444
[/TD]
[TD="align: right"]1.333
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]9.444
[/TD]
[TD="align: right"]9.111
[/TD]
[TD="align: right"]0.333
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]9.444
[/TD]
[TD="align: right"]9.399
[/TD]
[TD="align: right"]0.045
[/TD]

[TD="align: right"][/TD]

[TD="align: center"]13
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]9.444
[/TD]
[TD="align: right"]9.111
[/TD]
[TD="align: right"]0.333
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]9.444
[/TD]
[TD="align: right"]9.333
[/TD]
[TD="align: right"]0.111
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]9.444
[/TD]
[TD="align: right"]9.555
[/TD]
[TD="align: right"]1.111
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]9.777
[/TD]
[TD="align: right"]9.111
[/TD]
[TD="align: right"]0.666
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]9.777
[/TD]
[TD="align: right"]9.111
[/TD]
[TD="align: right"]0.666
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]9.777
[/TD]
[TD="align: right"]9.333
[/TD]
[TD="align: right"]0.444
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]9.777
[/TD]
[TD="align: right"]9.555
[/TD]
[TD="align: right"]0.222
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]20
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]9.777
[/TD]
[TD="align: right"]9.599
[/TD]
[TD="align: right"]0.178
[/TD]

[TD="align: right"][/TD]

[TD="align: center"]21
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]9.777
[/TD]
[TD="align: right"]9.888
[/TD]
[TD="align: right"]1.111
[/TD]

[TD="align: right"][/TD]

</tbody>
Data2



Note that the real input data is in columns A:E; column F is just a hand-typed set of values that I am HOPING I can achieve with M code, with your help.

Here is the M code for the full query taken from the Power Query editor:

Code:
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 from the code that the column added in the final step is intended 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 Ignore in the "Qualifier" column. If you try this code out on the data, you'll see that it does not achieve the desired results.

Please help.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
It's not pretty but this is one way of doing it.
First filter out ignore. Then replace null with zero if that is what you mean to do, otherwise filter out null.
Then reference this query.
Then group this query by Specific Identifier and return the minimum Candidate score and give the query a meaningful name.
Merge the two queries linking on the Specific identifier and Candidate score (use Ctrl to select second link).
Expand minimum.
Filter by not null.
Add custom column with formula ="Lowest".
Go back to original query, merge as new with the the query which has the column with Lowest in it.
Link again on Specific Identifier and Candidate Score.
Expand table to only return Custom column which will add Lowest.
Then tidy up by removing unwanted columns.
Peter
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,619
Latest member
Shiv1198

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top