Results 1 to 4 of 4

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

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    I have loaded a simple flat file I have pulled into a query. The data looks like this:


    A B C D E F
    1 Broad Identifier Specific Identifier Candidate Candidate Score Qualifier Candidate Score Evaluation, Desired Outcome
    2 9 9.333 9.111 0.222
    3 9 9.333 9.111 0.222
    4 9 9.333 9.777 1.444 Ignore
    5 9 9.333 9.333 Lowest
    6 9 9.333 9.555 1.222
    7 14 14.111 14.222 1.111 Lowest
    8 14 14.111 14.111 Ignore
    9 14 14.111 14.333 1.222
    10 14 14.111 14.444 1.333
    11 9 9.444 9.111 0.333
    12 9 9.444 9.399 0.045 Ignore
    13 9 9.444 9.111 0.333
    14 9 9.444 9.333 0.111 Lowest
    15 9 9.444 9.555 1.111
    16 9 9.777 9.111 0.666
    17 9 9.777 9.111 0.666
    18 9 9.777 9.333 0.444
    19 9 9.777 9.555 0.222 Lowest
    20 9 9.777 9.599 0.178 Ignore
    21 9 9.777 9.888 1.111 Ignore
    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.

  2. #2
    Board Regular
    Join Date
    Nov 2016
    Posts
    87
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Writing Power Query (M language) Table.X functions that will dynamically tailor the table scope to each row of 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

  3. #3
    New Member
    Join Date
    May 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Thanks, Peter. Another user got an answer to me on another forum already, and he provided code. I will have to take some time to think about it after hours, since I'm not a coder. But anyway, I dropped it in and it worked. Check it out at https://community.powerbi.com/t5/Des.../false#M334266

  4. #4
    Board Regular
    Join Date
    Nov 2016
    Posts
    87
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    OK
    Glad you found a solution elsewhere.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •