Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello Team,
I have two databases contains supervisor level from 1 to 6. Now I want to check if the user has the same supervisor level from both the databases or not. If all supervisor level matches, I want column to say match, else no match. Below is the example of two databases. So employee 2 is having match while employee 1 no match
Database:1
Employee | Super Level 1 | Super Level 2 | Super level 3 | Super level 4 | Super Level 5 |
1 | ABC | BDC | DVD | DDD | DGE |
2 | BGS | GGG | DED | DDD | DDD |
Database:2
Employee | Super Level 1 | Super Level 2 | Super level 3 | Super level 4 | Super Level 5 |
1 | ABC | BDC | DVD | DDD | RET |
2 | BGS | GGG | DED | DDD | DDD |
Hi @prakashmak,
Give this a go, merge your querys and see if the records values match
You can copy this example into a new blank query
let
DB1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0cgaSTi4g0iXMBUS6gEl3V6VYnWglI5CsezCQdHd3B4m7IqkBkrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, #"Super Level 1" = _t, #"Super Level 2" = _t, #"Super level 3" = _t, #"Super level 4" = _t, #"Super Level 5" = _t]),
DB2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0cgaSTi4g0iXMBUS6gMgg1xClWJ1oJSOQrHswkHR3dwfJuiLUgMjYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, #"Super Level 1" = _t, #"Super Level 2" = _t, #"Super level 3" = _t, #"Super level 4" = _t, #"Super Level 5" = _t]),
Source = Table.NestedJoin( DB1, {"Employee"}, DB2, {"Employee"}, "IsMatch", JoinKind.LeftOuter),
ReplaceValue = Table.ReplaceValue(Source,each [IsMatch], each List.IsEmpty( List.Difference( List.RemoveLastN( Record.ToList(_), 1), Record.ToList( [IsMatch]{0})) ),Replacer.ReplaceValue,{"IsMatch"})
in
ReplaceValue
It returns this result
Ps. If this helps solve your query please mark this post as Solution, thanks!
Hello, i am aware merge will work what are the other alternative apart from merge? if you have some details please share.
Hi @prakashmak
I can think of other ways but not necessarily better...
Why would you want/need to avoid a merge?
basically one source is SAP Hana and other is from Salesforce. When i publish them and set scheduled refresh, it throws error but when i refresh them without merge it worked.
Hi @prakashmak,
If you're encountering the error: "please rebuild this data combination"
See this article on how to resolve it:
https://excelguru.ca/power-query-errors-please-rebuild-this-data-combination/