Forum Discussion
Comparing two sets of columns from two tables
Hi,
Currently I have two tables that have addresses and dates. Table 1 looks likes this
| Address | Date |
| 123 green st | 1/2/21 |
| 456 red ave | 2/3/21 |
| 789 purple ter | 3/4/21 |
| 101 orange rd | 5/6/21 |
and table 2 is as follows
| address | date |
| 123 green st | 1/3/21 |
| 456 red ave | 2/1/21 |
| 789 purple ter | 3/4/21 |
| 101 orange rd | 5/6/21 |
| 102 brown rd | 6/6/21 |
What I need to do is create another table that has the addresses from table 1 and see if it exist in table 2. If it does, then if the date in table 1 is less then or equal to the date in table 2 for the same address then return signed if not return not signed.
so far i have a column that brings in the addresses from table 1, and now I am stuck with the "Signed" column to find out if it is signed or not.
Can someone please point me in the right direction? I tried writing an If statement but it would not let me compare values from two columns, it seems i have to compare the values in a column to a static value that I would input to the code.
Thank you in advance.
use a merge. In this example, TableA is the top, TableB is the bottom. Table Analysis is just a reference to TableA (you said you needed a 3rd table). Then I merged TableA and B on the address and returned the date from B, then did the comparison on the dates.
Here is my Excel file so you can look at the entire code and how it works.
4 Replies
use a merge. In this example, TableA is the top, TableB is the bottom. Table Analysis is just a reference to TableA (you said you needed a 3rd table). Then I merged TableA and B on the address and returned the date from B, then did the comparison on the dates.
Here is my Excel file so you can look at the entire code and how it works.
Hi Anonymous - just wanted to see if you had any trouble getting the file I linked to and seeing if you had any questions about it.
Anonymous DAX solution is atatched
Measure1 = //with relationship VAR _maxAddresst2 = CALCULATE ( MAX ( tbl2[date] ), tbl1 ) VAR _maxAddresst1 = MAX ( tbl1[Date] ) RETURN IF ( MAX ( tbl1[Address] ) <> BLANK (), IF ( _maxAddresst2 <= _maxAddresst1, "Signed", "Not Signed" ) ) Measure2 = //with or without relationship VAR __maxAddresst2 = CALCULATE ( MAX ( tbl2[date] ), TREATAS ( VALUES ( tbl1[Address] ), tbl2[address] ) ) VAR _maxAddresst1 = MAX ( tbl1[Date] ) RETURN IF ( MAX ( tbl1[Address] ) <> BLANK (), IF ( __maxAddresst2 <= _maxAddresst1, "Signed", "Not Signed" ) )Hello Anonymous ,
We can achieve it by duplicating Table1 and Applying merge query to it with Table2 on address columns after that in the custom column we need to apply simple if-else logic for dates comparison.
You can see the code for New Table below :
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZcsxCoAwDEDRq4TMQkyrVc/SdigYXKSWWD2/RXBy/Y/vPbKxsKlIhrNih6EyGTKMsfM4jA5UVki3NDJkP5jmBcqlZReooqGG/C/tsDR8B/cMh6a8CejaaCT3UnwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Address = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Address", type text}, {"Date", type date}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","789 purple ter#(tab)#(lf)789 purple ter#(tab)#(lf)","789 purple ter",Replacer.ReplaceText,{"Address"}),
#"Merged Queries" = Table.NestedJoin(#"Replaced Value", {"Address"}, Table2, {"Address2"}, "Table2", JoinKind.FullOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Address2", "Date2"}, {"Table2.Address2", "Table2.Date2"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Table2",{"Address", "Table2.Address2", "Date", "Table2.Date2"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Table2.Address2"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Address] <> null and [Address] <> "")),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Table2.Date2] <> null and [Table2.Date2] <> ""),
#"Added Custom" = Table.AddColumn(#"Filtered Rows1", "New", each if [Date] <= [Table2.Date2] then "Signed" else "Not Signed")
in
#"Added Custom"
Thanks,
Neel
