PowerQuery (in PowerBI) weird caching issue

0
votes

For the past couple of days, I have been trying to overcome a weird issue within Power Query in Power BI Desktop.

I have two tables, one is called the "Mappings" table, and the purpose of this table is to give me a column called "FMReference" after a bunch of transformations. Mappings Screenshot

I have my main table called "Entry", which DOESN'T HAVE FMReference, and thus, needs to be merged with the Mappings table. To make the merge simpler, I have created a simple column on both tables named "CombinedForMerge". Entry Screenshot

After the setup for the columns are complete, I proceed to create a Left Outer Merge with the Entry and Mappings tables, using the columns I have just made. Left Outer Merge Screenshot

As you can see on the screenshot, the FMReference column says A4.28, which is what I am expecting on my results. However, once I expand that merge to give me the FMReference, I am getting a different value, A3.2! Wrong Value Screenshot

Power BI clearly cached the wrong value on PowerQuery, as I have filtered the Mappings table to JUST one row, to get JUST the one value. Filtered Mappings Screenshot

I have tried clearing the cache from the Power BI Options, but this didn't change anything.

I've also tried rebuilding the Power BI report from a clean, new PBIX, just copying the transformations up to this point, but I am still getting the same issue.

I've tried copying the results of the Mappings table onto a static table, and used it for the merge instead. This worked, which clearly means it is something to do caching on the data transformations on Power BI.

I've refreshed the Preview, as well as Refreshed ALL a few times, but it still returns the wrong result.

I would appreciate any help at this point, the data is very sensitive so I cannot share a lot, but I am happy to give more details if needed.

Thanks in advance!

1
cachingpowerbipowerquery

1 Answers

0
votes

I ran into similar inexplicably odd behavior when using fuzzy merge, set for an exact match. I wound up swapping the tables and using a Right Outer merge instead. For some reason, that worked. I have no idea what caused the problem or why the Right Outer merge approach worked though. It was quite odd.

So what I'm saying is: Instead of starting your merge from XXXXEntry and matching to XXXXMappings with a Left Outer merge, you might try starting from XXXXMappings and matching to XXXXEntry with a Right Outer merge.

One way to do that, if you still want the merge in the same place in the same query, is to just edit your existing query's code in the formula bar. To do that, click on the Applied Step that is associated with the merge, most likely called Merged Queries; then in the formula bar, swap the references to the tables and change JoinKind.LeftOuter to JoinKind.RightOuter. (One table reference will likely be the name of the Applied Step before your Merged Queries step, instead of XXXXEntry. For me, in the before and after examples that follow, it was #"Changed Type".)

Before:

= Table.NestedJoin(#"Changed Type", {"CombinedForMerge"}, XXXXMappings, {"CombinedForMerge"}, "Changed Type", JoinKind.LeftOuter)

After:

= Table.NestedJoin(XXXXMappings, {"CombinedForMerge"}, #"Changed Type", {"CombinedForMerge"}, "XXXXMappings", JoinKind.RightOuter)

Good luck.