Forum Discussion
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, give me as result the manager's manager.
Here the data sample and the expected results.
SEARCH MANAGER ID IN THE EMPLOYEE ID COLUMN, IF EMPLOYEE ID IS INACTIVE, RESULT IS MANAGER'S ID , ELSE, DO NOTHING.
| EMPOLYEE ID | STATUS | MANAGER |
| 1 | active | 2 |
| 2 | active | 1 |
| 3 | active | 6 |
| 4 | active | 6 |
| 5 | active | 7 |
| 6 | INACTIVE | 8 |
| 7 | INACTIVE | 9 |
| 8 | active | 10 |
| 9 | active | 10 |
| 10 | active | 10 |
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.
12 Replies
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.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?
- 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
Hi Mariusz , I understand your solution and accept it, only question is, how do in change this code:
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpMLsksSwUyjJRidaKBJJKQIVjIGFnIDCxkgilkiixkDhYyA7I8/RydQzzDXIFMC7CgOaqgJVjQAsVWA7CYJRYxIIUmGAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type textto capture my actual data?
I suppose that "BinaryFromText" was generated somehow, right?
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