Relationship Issues

[AgingRapidly]

I've got the four queries shown in data model screenshot at the bottom of this post.

I can't work out how to connect the WarehouseQuantities table.

When I try to connect WarehouseQuantities to either PO_Detail-DIM or PO_Detail-FACT it isn't allowed because of duplicate values. There are items for which there are inventory quantities in multiple warehouses. E.g.,


I'm trying to add QuantityOnHand to this pivot table. I've created the measure, but of course it doesn't work because there is no relationship.



My data source for WarehouseQuantities has the quantity values I'm pulling from PO_Detail--FACT, but it doesn't include the PurchaseOrderNo.

Am I going at this the wrong way? Any help will be greatly appreciated.

 

[Ken Puls]

Hi Melinda,

As your WarehouseQuantities is a fact table, you definitely don't want to link it to PO_Detail_Fact. But if you have duplicate ItemCode values in the PO_Detail-DIM, I would set up a bridge table for Items that can filter both PO_Detail-DIM and WarehouseQuantities. I'm pretty sure you have a recipe card for that from a course you attended, but let me know if you can't find it.

 

[AgingRapidly]

Ken,
Thanks for the response. Sorry it has taken me so long to get to try it out.

I must be doing something wrong. No surprise there.

This is what I've tried

1. I referenced Raw Data query IM_ItemWarehouse. Removed all columns except ItemCode. Removed duplicates. Detected data type. Renamed query as IM_ItemCode4Bridge.
2. I referenced Raw Data Query PO_PurchaseOrderDetail. Removed all columns except ItemCode. Removed duplicates. Detected data type. Renamed query as PO_Detail4Bridge
3. I selected PO_Detail4Bridge. Selected Append Query as New. Selected IM_ItemCode4Bridge as second query. Attempted to remove duplicates, but got the error message Below. I don't have a column named "t0_1" in either table.

Thanks again,
Melinda

 

[Ken Puls]

Hmm... that is strange. t_01 sounds like a temporary column name that Power Query creates for its query folding...

One thing I wonder is whether you can go back to the IM_ItemWarehouse and Query PO_PurchaseOrderDetail tables, right click the ItemCode column in each and change the columns to Capitalize Each Word. This may have no effect whatsoever, but it may just kick something loose for Power Query by rebuilding that query (and it's a good thing to do to prevent duplicates in Power Pivot that Power Query doesn't see as duplicates.

Let me know if that fixes anything, or if it still complains...

 

[AgingRapidly]

Hmm... that is strange. If I had a nickel for everytime our IT people have told me that ...

I found the problem. I mistakenly made changes to the Raw Data PO_PurchaseOrderDetail query which broke the query folding.

I've still got a problem in that I've got the dreaded yellow box Relationships between tables may be needed.

If I get time today, I'll use the Monkey Tools to find out where else I've gone wrong.