Forum Discussion

stefano86's avatar
stefano86
New Member
1 year ago
Solved

Excel Power Query - how to make my tables work?

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!

 

Need Help
  • pmreis's avatar
    pmreis
    1 year ago

    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

2 Replies

  • pmreis's avatar
    pmreis
    Most Valuable Professional
    1 year ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.