how to make conditional column using two tables with reference to another table

Hi Nicks612 ,

How about this:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc47EoQwDAPQu6SmkCV/krMw3P8ahF1mQgGt30jWvjejPLJtjaAQEAbDqx3bm3UvxocVhsbPqMjqy7oBprtTfOboVizzV5t3i7hzOVetzilE+H9LTRPXvzIhPb/McNlxAg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Sr.No = _t, DBKey = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sr.No", Int64.Type}, {"DBKey", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Sr.No"}, TableB, {"Sr.No"}, "TableB", JoinKind.LeftOuter),
    #"Expanded TableB" = Table.ExpandTableColumn(#"Merged Queries", "TableB", {"Status"}, {"New Status"})
in
    #"Expanded TableB"

The trick is to use the merge function:

let me know if this helps you 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

Nicks612 

status_rec = Record.FromList(tblB[Status], List.Transform(tblB[Sr.No], Text.From)),
add_col = Table.AddColumn(tblA, "New Status", each Record.FieldOrDefault(status_rec, Text.From([Sr.No])))

Hello guys,

tackytechtom  and AlienSx 

sorry for the inconvenience!! there is some change in the previous data query. Please find the updated doubt.

I have two tables: table A and table B as shown below.

I want to make a conditional column in Table B with reference to Table A which contains OK and Nok data.

Table A contains:

Table B contains:

Result table B be like:

Can you help me out with this problem? I have tried with the DAX lookup formula and succeeded but need to do it with the power query editor.