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

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi all,

I need a little bit more help on this solution I do not understand what to replace.

This is the code I get when I do the following:

 

  1. FIlter all Inactive Employees
  2. Merge Queries
  3. Select "Manager" from the first table
  4. Select "Employee" from the second table

I do not know what do to do next 😞 , I am not sure how to read your steps when it comes to  <<Select Merged Queries Step from applied steps, go to the formula bar and replace #"Filtered Rows" with the step before>>

 

My code looks like this

   #"FILTERED INACTIVE" = Table.SelectRows(#"Cleaned Text", each ([STATUS] = "INACTIVE")),
    #"Merged Queries1" = Table.NestedJoin(#"FILTERED INACTIVE",{"MANAGER"},#"FILTERED INACTIVE",{"USERID"},"FILTERED INACTIVE",JoinKind.LeftOuter)

Am I reading something wrong from you steps?

Thanks

Luca

Mariusz's avatar
Mariusz
Community Champion
6 years ago

Hi Anonymous 

 

Point 3 is suggesting to replace manually the first table expression ( step = #"FILTERED INACTIVE" ) with the one before in the Merge Step, please see the red circle on the screenshot from my previous post, 

 

If you still struggling, you can post your M from the Advanced Editor and I can do it for you.

 

Best Regards,
Mariusz

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