Forum Discussion

prakashmak's avatar
prakashmak
New Member
3 years ago

Match supervisor levels from two databases

  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 ma...
Need Help
m_dekorte's avatar
m_dekorte
Resident Rockstar
3 years ago

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!