Match supervisor levels from two databases

[prakashmak]

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

[m_dekorte]

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]

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

[m_dekorte]

Hi @prakashmak

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

Why would you want/need to avoid a merge?

[prakashmak]

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.

[m_dekorte]

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/