Inconsistent Data When Using Relationship on Index column between a Merged Table and a Source Table

[carrienorris]

For my data model, I added two tables, Order_Transaction and Product_DetailLastLevel which are MS SQL Queries. The data is imported into Power BI.

On Order_Transaction, I added an index column starting at 1.

I then used the Merge feature to merge the Order_Transaction table with the Product_DetailLastLevel table on the SKU column. I expanded all the merged columns. Works great.

The new merged table breaks the data out into the product components (for instance on product bundles, now instead of 1 line for the purchase of a bundle, might have 3 lines for that one purchase, one for each component of the bundle.)

After applying changes, in the relationships, I created a relationship between Order_Transaction and Merge1 on the Index column.

Now in a table visual, I add some columns from both these tables and there's some serious weirdness happening. From both tables I added Index and Order ID.

As shown above, none of the order id's seem to match their index. When I go back to the query and filter for a specific index in both the Order_Transaction table and the Merge1 table to check the data, the data matches between the two tables, it just doesn't show the right data on the table visual.

Does anyone know what's happening? Did I miss a step somewhere? Can I not rely on a relationship on Index columns?

Any help is appreciated.

Carrie

[some_bih]

Hi @carrienorris my best practice when I merge columns in PQ: write number of rows before any "transformation" per table / columns. During merging if you notice PQ inform you how many rows are mathed from one table to another one. If counting rows before and after merging do not fit / make sense then depending on your need change type of join (left to rights etc) and for sure check are there in columns some blank rows or similar. 

Sometimes, it is expected to have "lower" number of matched rows between tables as it is possible that like some products are never /not sold but this product is in catalogue. I hope this help