Forum Discussion
Help with query for evaluating a condition between two tables involving multiple columns
Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
HI lbendlin ,
Please see the sample data as follows:
Table A
| Ticket created | Ticket solved | Submitter ID |
| 2022-07-09T00:47:03 | 2022-07-09T00:47:30 | 123456789101 |
| 2022-07-09T00:47:03 | 2022-07-09T00:47:30 | 112131415161 |
| 2022-07-09T00:47:03 | 2022-07-09T00:47:30 | 718192021222 |
Table B
| Submitter name | DOJ | DOL | Submitter ID |
| User A | 01/01/01 | 12/31/99 | 123456789101 |
| User B | 01/01/01 | 08/28/20 | 112131415161 |
I require a custom column in Table A to return a Yes / No or 0 / 1 depending on the following conditions to match:
1) Submitter ID in Table A exists in Table B
2) 'Ticket created' for this submitter ID in Table A should be in between DOJ and DOL dates in Table B
and would like to achive the above using M query if at all possible.
Expected Outcome in Table A
| Ticket created | Ticket solved | Submitter ID | CustomColumn |
| 2022-07-09T00:47:03 | 2022-07-09T00:47:30 | 123456789101 | Yes |
| 2022-07-09T00:47:03 | 2022-07-09T00:47:30 | 112131415161 | No |
| 2022-07-09T00:47:03 | 2022-07-09T00:47:30 | 718192021222 | No |
Any help would be appreciated.
Table A:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lcuxDQAhEAPBXi4GyfYBB9TxGaL/NiD/iHRWu5YJUkZkjA+YJSbc0l8dVykvtUUfBG2nx5mis7Cyvc/BznETJdneBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Ticket created" = _t, #"Ticket solved" = _t, #"Submitter ID" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ticket created", type datetime}, {"Ticket solved", type datetime}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (k)=> if Table.RowCount(Table.SelectRows(#"Table B",each k[Submitter ID]=[Submitter ID] and k[Ticket created]>DateTime.From([DOJ]) and k[Ticket created]<DateTime.From([DOL]) )) > 0 then "Yes" else "No") in #"Added Custom"Table B:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi1OLVJwVNJRMjDUByMg09BI39hQ38gIzDQ2MTUzt7A0BMrE6kDVO6GqN7DQNwIiA5B6QyNDY0MTQ1NDM6D6WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Submitter name" = _t, DOJ = _t, DOL = _t, #"Submitter ID" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"DOJ", type date}, {"DOL", type date}}) in #"Changed Type"Note: "12/31/99" is a VERY bad choice for a dummy date. Better leave it blank.