Excel Power Query - how to make my tables work?

[stefano86]

Good day everyone,

this is my first post, and I thank you in advance for the time you will dedicate to help me understand my issue.

I have the need to compare inventories from 2 different systems called SeaCare (SC) and CrunchTime (CT). Each inventory table contains items which do repeat, since they are result of appending inventory tables from the different storage locations (Vessels). This happens for both SC and CT tables.

Here attached you will find, the tables I have so far:

on the left you can see Medication List and Supply List tables. These are obtained for every storage location (Vessel) and, after appending them, the SeaCare_FULL_Inventory table is created.

on the right side you can see the Costa and Aida tables, which contain inventory tables from CT. They are also appended to create the CT_FULL_Inventory table.

so, being the SeaCare_FULL_Inventory and the CT_FULL_Inventory the result of all other reports, I'd need to compare these two.

In the particular:

• Starting from the Total Quantity value of CT_FULL_Inventory, we need to substract the Quantity value from the SeaCare_FULL_Inventory table, for the exact storage location
• Being the items not unique, I have created a field called Unique SKU, which is done by concatenating the storage location (Vessel) and the SKU of the product. I have tried to create the relation between these two fieds but it doesn't work being 1to*.

Obviously there's something I am missing here.

I hope my explanation was good enough, please let me know in case of questions.

thanks all!

[Anonymous]

Hi  @stefano86 , 

Thanks for reaching out to our community.

Have you tried using Merge Queries in Power Query?

Here're the steps:

Choose the SeaCare_FULL_Inventory table as the first table and the CT_FULL_Inventory table as the second table.
Merge the tables on the Unique SKU field. Then you can expand the column with tables. Add a custom column with [Total Quantity] - [Quantity]. 

Hope it helps.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[pmreis · Accepted solution]

Hi @stefano86 

Add a table with the Unique products. You can make it preferably in Power Query, but for the simplicity and to make it easier to test here it is in DAX:

Unique_SKU_Table = 
DISTINCT(
    UNION(
        SELECTCOLUMNS(
            SeaCare_FULL_Inventory,
            "Unique SKU", [Unique SKU],
            "Vessel", [VESSEL],
            "Product Name", [NAME],
            "Category", [CATEGORY]
        ),
        SELECTCOLUMNS(
            CT_FULL_Inventory,
            "Unique SKU", [Unique SKU],
            "Vessel", [Vessel],
            "Product Name", [Product Name],
            "Category", [CATEGORY]
        )
    )
)

Then create metrics on this new table, to calculate the quantity on each related table.

Total_SeaCare_Quantity =
CALCULATE(
SUM(SeaCare_FULL_Inventory[QUANTITY]),
RELATEDTABLE(Unique_SKU_Table)
)

Total_CrunchTime_Quantity =
CALCULATE(
SUM(CT_FULL_Inventory[Total Quantity]),
RELATEDTABLE(Unique_SKU_Table)
)

Pedro Reis - Data Platform MVP / MCT
Making Power BI and Fabric Simple

If my response resolved your issue, please mark it as a solution to help others find it. If you found it helpful, please consider giving it a kudos. Your feedback is highly appreciated!

Find me at LinkedIn