Power Query returns incorrect results when merging and comparing two tables, but only sometimes

[SevsBo]

I have Table A and Table B with one column in common, let's call it City, and the entires in that column are distinct in both tables - there are no duplicates, but they sometimes repeat across the tables. Both tables have Date and Rating as columns as well.

As the tables come from different sources, I can only combine them in Power Query, and as the data in one might be newer than in another I have a conditional check performed after the merge.

The steps are:

- ....

- Merge Table A and Table B on City

- Expand Table B, columns Table_B.Date and Table_B.Rating

- Create Compare column: if City is not null in Table B AND Table B Date is greater than Table A Date, then "Yes", otherwise "No"

- Filter the result by Compare column, only take No

In essence I'm trying to remove any entries in Table A that have newer entires for the same City in Table B.

For vast majority of cases, this works perfectly fine, but I am noticing a small amount of entries where the relevant fields do not get populated and thus the entries are erroniously kept instead of being removed.

Let's say I have a Chichago as a city, that I can clearly see in Table B in Data View, with the relevant columns populated, but for some reason those columns do not get populated in the Merge - Expand part of the Power Query above. This means it gets left in Table A when it should have been removed. There are a few dozen examples of this in a data set of a few dozen thousand, but this is causing issues when creating custom tables later on.

I have so far tried to add Table.Buffer to the Expand part of the query, but as I write the Load is still "Waiting for other queries", so I have doubts about its efficiency, if it even works.

Is there any other way I can ensure that the Merge - Expand part of the query is working fine, as it clearly isn't at the moment?

[Anonymous]

Your solutions is great @lbendlin , It works very well!

Hi, @SevsBo 

After my tests, the M code of super user works very well:

Since the sample data you provided only has months in English, in the comparison, as Super User said, January is smaller than August. 

You can download the test PBIX file for this use below.

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SevsBo]

Hi @lbendlin ,

This is as close as I can get to providing sample data without including sensitive data:

Table_1

City	Rating	Date_Updated
Washington	1	August
Denver	1	August
New York	1	August
Miami	1	August
Los Angeles	1	August

Table_2

City	Rating	Date_Updated
Paris	2	January
Washington	2	January
London	2	January
Miami	2	September
Los Angeles	2	September

Expected result after merge & conditional steps, but before filtering:

City	Rating	Date_Updated	Table_2.Rating	Table_2.Date_Updated	Conditional_colum_result
Washington	1	August	2	January	NO
Denver	1	August	null	null	NO
New York	1	August	null	null	NO
Miami	1	August	2	September	YES
Los Angeles	1	August	2	September	YES

In essence, YES means that corresponding data was found in Table_2 and that the Table_2.Date_Updated is newer than Table_1.Date_Updated. This is the results I want to filter out in the next step, so expected result would be the first three City elements.

Expected result after filtering:

City	Rating	Date_Updated	Table_2.Rating	Table_2.Date_Updated	Conditional_colum_result
Washington	1	August	2	January	NO
Denver	1	August	null	null	NO
New York	1	August	null	null	NO

Example of actual result after filtering:

City	Rating	Date_Updated	Table_2.Rating	Table_2.Date_Updated	Conditional_colum_result
Washington	1	August	2	January	NO
Denver	1	August	null	null	NO
New York	1	August	null	null	NO
Miami	1	August	null	null	NO

The merge & conditional column are done on Query level, and I can see that Miami has correct values as outlined above, in both tables, but for some reason the end merged table doesn't pick up the values, even if I do a full refresh of all data sources.

PS: I have tried creating better tables, but I keep running into this error:

Your post has been changed because invalid HTML was found in the message body. The invalid HTML has been removed. Please review the message and submit the message when you are satisfied.

[lbendlin · Accepted solution]

Why is "January"  not greater than "August" ?  Alphabetically it is.  If you want to compare dates you need to use dates.

Table_1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk8szsjMSy/Jz1PSUTIEYsfS9NLiEgVDpVidaCWX1Lyy1CJsMn6p5QqR+UXZ2OR8MxNzM7FJ+OQXKzjmpafmpBZjSMcCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [City = _t, Rating = _t, Date_Updated = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date_Updated", type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"City"}, Table_2, {"City"}, "Table_2", JoinKind.LeftOuter),
    #"Expanded Table_2" = Table.ExpandTableColumn(#"Merged Queries", "Table_2", {"Rating", "Date_Updated"}, {"Rating.1", "Date_Updated.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table_2", "Conditional_column_result", each if [Date_Updated.1]<>null and [Date_Updated.1]>[Date_Updated] then "YES" else "NO"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Conditional_column_result] = "NO"))
in
    #"Filtered Rows"

Table_2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgsyixW0lEyAmKvxLzSxKJKBUOlWJ1opfDE4ozMvPSS/Dys0j75eSk4pHwzE3MzoTLBqQUlqblJqUVwbcUKjnnpqTmpxdhUxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [City = _t, Rating = _t, Date_Updated = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date_Updated", type date}})
in
    #"Changed Type"

[kleigh]

If the join is not working and the two values look the same, could it be some whitespace sneaking in? Try running Text.Trim on the columns.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...