Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Search Results within the same query

Hi, I have a very basic question. I want to create a conditional column that does a "sort" of VLOOKUP witihin the query itself ...!   I need to see if an employee's manager is INACTIVE and, if yes...
Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi Anonymous 

     

    Try the below if it works for you 

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpMLsksSwUyjJRidaKBJJKQIVjIGFnIDCxkgilkiixkDhYyA7I8/RydQzzDXIFMC7CgOaqgJVjQAsVWA7CYJRYxIIUmGAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"EMPOLYEE ID" = _t, STATUS = _t, MANAGER = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EMPOLYEE ID", Int64.Type}, {"STATUS", type text}, {"MANAGER", Int64.Type}}),
    #"Next Line Up Menager" = 
        let 
            #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([STATUS] = "INACTIVE")),
            #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"MANAGER"}, #"Filtered Rows", {"EMPOLYEE ID"}, "Changed Type", JoinKind.LeftOuter)
        in
            #"Merged Queries",
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Next Line Up Menager", "Changed Type", {"MANAGER"}, {"Next Line Up Menager"})
in
    #"Expanded Changed Type"
    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

Mariusz's avatar
Mariusz
Community Champion
6 years ago

Hi Anonymous 

 

Try the below if it works for you 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpMLsksSwUyjJRidaKBJJKQIVjIGFnIDCxkgilkiixkDhYyA7I8/RydQzzDXIFMC7CgOaqgJVjQAsVWA7CYJRYxIIUmGAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"EMPOLYEE ID" = _t, STATUS = _t, MANAGER = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EMPOLYEE ID", Int64.Type}, {"STATUS", type text}, {"MANAGER", Int64.Type}}),
    #"Next Line Up Menager" = 
        let 
            #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([STATUS] = "INACTIVE")),
            #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"MANAGER"}, #"Filtered Rows", {"EMPOLYEE ID"}, "Changed Type", JoinKind.LeftOuter)
        in
            #"Merged Queries",
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Next Line Up Menager", "Changed Type", {"MANAGER"}, {"Next Line Up Menager"})
in
    #"Expanded Changed Type"
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi and thank you Mariusz.

 

Can you please expand a little bit on the propsed solution, from the code you pasted, I was trying to understnad where the actual "magic" happens, is it here?

 

Next Line Up Menager" = 
        let 
            #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([STATUS] = "INACTIVE")),
            #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"MANAGER"}, #"Filtered Rows", {"EMPOLYEE ID"}, "Changed Type", JoinKind.LeftOuter)
        in
            #"Merged Queries",
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Next Line Up Menager", "Changed Type", {"MANAGER"}, {"Next Line Up Menager"})
in
    #"Expanded Changed Type"

 

 Are you performing a merge within the same query?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Hi Anonymous

    In PBI it is possible to encapsulate a section of code withing "let-in" group. In most cases it just makes the query look tidier in the editor.

    In the code you have cited the bit relating to creating (filtering) a table that contains inactive employees and then merging it to the original table.

    It is possible to re-write it to:
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([STATUS] = "INACTIVE")),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"MANAGER"}, #"Filtered Rows", {"EMPOLYEE ID"}, "Changed Type", JoinKind.LeftOuter),
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"MANAGER"}, {"Next Line Up Menager"})
    in #"Expanded Changed Type"

    Kind regards,
    JB
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you Anonymous , very clear.

      I guess I now need one last "bit" of help with the code.

       

      I have to adapt the "Table from Rows" to my current query, therefore the M code will have to be different.

      I looked up the syntax of the function "Table.fromRows", but I cannot figure out how to tell PBI to select my entire current table and create the "sub-" table where the "Next Line Up Manager" data are created.

       

      This section of the code below, is recreating the sample I have put in my first post, but I now need to use my real data.

      Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpMLsksSwUyjJRidaKBJJKQIVjIGFnIDCxkgilkiixkDhYyA7I8/RydQzzDXIFMC7CgOaqgJVjQAsVWA7CYJRYxIIUmGAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"EMPOLYEE ID" = _t, STATUS = _t, MANAGER = _t])

       

      Thanks!