Forum Discussion
Power Query returns incorrect results when merging and comparing two tables, but only sometimes
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?
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"
5 Replies
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-Forum/ba-p/963216
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/1447523Hi 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.
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"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.
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.
