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

[Nicks612]

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

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

Table A contains:

Sr.No	DBKey
123456	20230503092547
123456	20230503084725
123456	20230503070939
235678	20230508100137
132456	20230524172714
132456	20230527141557
165478	20230514120545
765432	20230507130646
765432	20230507131046

Table B contains:

Sr.No	DBKey	Status
123456	20230503084725	Nok
235678	20230508100137	Ok
132456	20230527141557	Nok
765432	20230507131046	Nok

Result table A be like:

Sr.No	DBKey	New Status
123456	20230503092547	Nok
123456	20230503084725	Nok
123456	20230503070939	Nok
235678	20230508100137	Ok
132456	20230524172714	Nok
132456	20230527141557	Nok
165478	20230514120545
765432	20230507130646	Nok
765432	20230507131046	Nok

Can anyone 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.

[Nicks612]

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:

Sr.No	Status	DBKey
123456	OK	20230503092547
123456	NG	20230503084725
123456	OK	20230503070939
235678	NG	20230508100137
132456	NG	20230524172714
132456	OK	20230527141557
165478	OK	20230514120545
765432	NG	20230507130646
765432	NG	20230507131046

Table B contains:

Sr.No	DBKey
123456	20230503084725
235678	20230508100137
132456	20230527141557
765432	20230507131046

Result table B be like:

Sr.No	DBKey	New Status
123456	20230503084725	NG
235678	20230508100137	NG
132456	20230527141557	OK
765432	20230507131046	NG

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.

[v-cgao-msft · Accepted solution]

Hi @Nicks612 ,

Please try using merge query

and then transform the New Status column.

In the simulated data, there will be the case of matching to more than one status, here I assume that there is "OK" in the status to return "OK", otherwise return "NG".

Please check the pbix file.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[Nicks612]

Thanks a lot for the prompt response!

[AlienSx]

@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])))

[tackytechtom]

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/