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 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

EmployeeSuper Level 1Super Level 2Super level 3Super level 4Super Level 5
1ABCBDCDVDDDDDGE
2BGSGGGDEDDDDDDD

 

Database:2

EmployeeSuper Level 1Super Level 2Super level 3Super level 4Super Level 5
1ABCBDCDVDDDDRET
2BGSGGGDEDDDDDDD

 

 

 

 

 

 

 

 

Need Help

5 Replies

  • 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!

    • prakashmak's avatar
      prakashmak
      New Member
      3 years ago

      Hello, i am aware merge will work what are the other alternative apart from merge? if you have some details please share.

       

      • m_dekorte's avatar
        m_dekorte
        Resident Rockstar
        3 years ago

        Hi prakashmak

         

        I can think of other ways but not necessarily better...

        Why would you want/need to avoid a merge?