Coming up with a lookup table for suppliers and purchase order numbers

[Anonymous]

Hi there,

I am making a little report for management to get a quick view of invoicing from our purchasing system (it doesn't have great reports).

I have two tables, the Purchase Order table and the Invoice table.

What I would like to do is create a lookup / DIM table for the suppliers and the purchase order numbers from the Purchase order table, then use those in the relationship between the two tables.

I don't want to create these manually as when we use new suppliers, the purchase numbers increase I just want that to dynamically update the lookup tables.

I assume I can just have some sort of measure for each of these that will pull out the unique values for the supplier from the Purchase table and a measure that can just keep building a list of PO number also.

I am just not savvy enough with this to be able to create these measures.

Could someone even just let me know the commands in the measure to use then I will go on a hunt and see what I need to do.  I assume it is DISTINCT but I keep coming up with a count or just errors?!  😕

Thank you.

[v-janeyg-msft]

Hi, @Anonymous 

Why distinct can't work? How did you write it?

Can you provide some sample data and your desired result? So we can help you soon.

Best Regards,

Community Support Team _ Janey

[v-janeyg-msft]

@Anonymous   Any updates?

If problem still persists, pls let me know.

Best Regards,

Community Support Team _ Janey

[TheoC]

@v-janeyg-msft resolution through SUMMARIZE to establish new entity given the nature of the data 🙂

[Anonymous]

hey there,

I have a date table in place, I make sure I have that created each time.

I actually think there are some legacy data issues with this system.  Looking at the raw exported date there are multiple lines for the same Purchase order which is why I am getting errors about the relationship cardinality.  I will try a remove duplicates on the imported data and see what I come up with.  Editing this, there are no duplicates, needed to go wider in my view.

So I have an Order Number column that has duplicates and get cardinality errors when trying to join it to the order number in the Invoice table also?  

Bit stuck really.

[TheoC]

Hi @Anonymous,

Best way to clean it up is in Power Query. However, just be sure that you are removing duplicates and not  Purchase Orders that have been replaced with updated descriptions, etc. 

For example, if PO12345 has the details:

• PO Date: 1/10/2021
• PO Description: 10 pizzas
• PO Supplier: Dominos

then the duplicate PO12345 should have the same details, otherwise you most likely want to keep the records.

The other way in which you may be able to remove "legacy" data is by only selecting Purchase Orders that had a PO Date / PO Creation Date that was created post-implementation of the new system.  By doing this, you are excluding legacy data and legacy issues. 

Importantly, note that the cost of a reduced population of data when using the PO Creation Dates as a baseline (as mentioned above) will be netted off against the enhanced integrity and reliability in the outputs from your analysis. For example, if legacy data is the issue and it is excluded, then the data that is included is likely to contain less inaccuracies than what it would have otherwise. However, the cost is that you have less data available to analyse.

Hope the above is of value and don't hesitate to touch base if there's anything I or the Community can assist with further. 

Theo 🙂

[Anonymous]

it's frustrating that the supplier is not in the invoice table and I need to rely on the purchase order number but I can't build a relationship.

[TheoC]

Hi @Anonymous 

If you want a Supplier matched to a Purchase Order, you can use the SUMMARIZE function as a "New Table" (similar to that of a New Measure which is under the Modelling tab in the ribbon).  

Something such as the following can be used to allocate a Supplier to a Purchase Order:

Table 2 = SUMMARIZE( 'tblPurchaseOrder' , tblPurchaseOrder[PurchaseOrder], tblPurchaseOrder[SupplierID or SupplierName] )

You may need to adjust the syntax but all in all it should give you the ability to create a relationship from the "Supplier" table to the "PO table" and then to the "Invoice table".

Hope it helps mate 🙂

(Apologies for adding this here, apparently hit the limit for # of private messages 😂)

[TheoC]

Hi @Anonymous 

A couple of things to be very cautious about:

1. Does your accounting system one Purchase Order to have many Line Items (i.e. like S4HANA or other larger systems as such)? If so, consider "concatenating" the PO and Line Item columns as one (i.e. create a new Calculated Column using "PO by Line Item = 'PurchaseOrderTable'[PurchaseOrder] &" "& 'PurchaseOrderTable'[LineItem]" in both your Purchase Order Table and repeating the process in your Invoice Table.
2. If the above is not relevant, you can simply use the Purchase Order Number from the Purchase Order Table and drag it to the Invoice table's Purchase Order Number. It should create a one to many relationship like below. 
3. If you don't have Date's table, I recommend you create one, even if it's a simplified version (https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/).  

All in all, to get the information you seek, you do not need to do any formulation / calculations if the Purchase Order Number is the "Primary Key"