I have loaded a simple flat file I have pulled into a query. The data looks like this:
<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>
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:
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.
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.