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   

This is generated when you use the "Enter Data" functionality you don't need to worry about this bit.

All you need to do is a few extra steps in your table.

1. filter your table to All Inactive

2. Use Merge Queries 

On Current Table, with join on Manager and Employee ID

 

3.  Select Merged Queries Step from applied steps, go to the formula bar and replace #"Filtered Rows" with the step before.

 

4. last step is to expand your tables.

 

Hope this helps

Mariusz

Anonymous

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hello Mariusz 

thank you so much for the clear help.

This key piece of training is extremely useful

I will now try your steps, but before I do I have a question,  about step 1: if I filter my table by Inactive before I merge, I have then to unfilter it afterwards , otherwise my final results will be wrong, or is this done in step 3 in your instructions?

 

thanks a mill!