Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Comparing two excel sheets without merging

Hello,   I'm dealing with two warehouses that use different systems for item tracking. I receive weekly reports from each warehouse, and I just need to set up a dashboard that shows the differences...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    You have to filter out the rows containing "in transit":

    try:

    Table differences =
VAR receiving1 =
    SUMMARIZE (
        Receiving,
        Receiving[ItemID],
        Receiving[Description],
        Receiving[Item Type],
        "QT", SUM ( Receiving[Quantity])
    )
VAR sending1 =
    SUMMARIZE (
        FILTER(
        Sending, Sending[Status] <> "In transit"),
        Sending[ItemID],
        Sending[Description],
        Sending[Item Type],
        "QT", SUM ( Sending[Quantity (all)] )
    )
VAR OnlyRec =
    ADDCOLUMNS ( EXCEPT ( receiving1, sending1 ), "From table", "Receiving" )
VAR OnlySend =
    ADDCOLUMNS ( EXCEPT ( sending1, receiving1 ), "From table", "Sending" )
RETURN
    UNION ( OnlyRec, OnlySend )
PaulDBrown's avatar
PaulDBrown
Community Champion
5 years ago

You could create a new table to identify the rows which are not in the other table using something along the lines of:

 

Table differences =
VAR receiving1 =
    SELECTCOLUMNS (
        Receiving,
        "ItemID", Receiving[ItemID],
        "Description", Receiving[Description],
        "Item Type", Receiving[Item Type],
        "QT", Receiving[Quantity]
    )
VAR sending1 =
    SUMMARIZE (
        Sending,
        Sending[ItemID],
        Sending[Description],
        Sending[Item Type],
        "QT", SUM ( Sending[Quantity (all)] )
    )
VAR OnlyRec =
    ADDCOLUMNS ( EXCEPT ( receiving1, sending1 ), "From table", "Receiving" )
VAR OnlySend =
    ADDCOLUMNS ( EXCEPT ( sending1, receiving1 ), "From table", "Sending" )
RETURN
    UNION ( OnlyRec, OnlySend )

 

 

Which will get you this:

 

I've attached the sample PBIX file for your reference

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thanks PaulDBrown 


This is truly impressive. You've really shown your expertise on PBI. 

 

However, in my test, I found if in the receiving table they recorded 1 item per 1 line (they have done that sometimes), the difference table considers this as only 1 in quantity. See below, the item actually appears in 7 different lines in the receiving table, so technically it shouldn't appear on this table at all (There are 7 items with the ID 6908 in both tables).

 

When I show the QT column as a sum, the table shows the correct number, but this item shouldn't be here at all. See below:

 

 

Any suggestions?

 

Thanks, 

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    5 years ago

    In that case you need the SUMMARIZE function in the first VAR too.

     

     

    Table differences =
VAR receiving1 =
    SUMMARIZE (
        Receiving,
        Receiving[ItemID],
        Receiving[Description],
        Receiving[Item Type],
        "QT", SUM ( Receiving[Quantity])
    )
VAR sending1 =
    SUMMARIZE (
        Sending,
        Sending[ItemID],
        Sending[Description],
        Sending[Item Type],
        "QT", SUM ( Sending[Quantity (all)] )
    )
VAR OnlyRec =
    ADDCOLUMNS ( EXCEPT ( receiving1, sending1 ), "From table", "Receiving" )
VAR OnlySend =
    ADDCOLUMNS ( EXCEPT ( sending1, receiving1 ), "From table", "Sending" )
RETURN
    UNION ( OnlyRec, OnlySend )

     

     

    try that and see if it works.

     

    BTW, since this is table, you can create measures for example to calculate the balance by row as a summary

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Amazing! Thanks PaulDBrown 

      One final question, in my sending table I have a "Status" column that shows the item's current status. Do you have a recommendation for me to integrate it to this table differences, so it ignores items that has the status "In transit"?

      Thanks, 

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        5 years ago

        You have to filter out the rows containing "in transit":

        try:

        Table differences =
VAR receiving1 =
    SUMMARIZE (
        Receiving,
        Receiving[ItemID],
        Receiving[Description],
        Receiving[Item Type],
        "QT", SUM ( Receiving[Quantity])
    )
VAR sending1 =
    SUMMARIZE (
        FILTER(
        Sending, Sending[Status] <> "In transit"),
        Sending[ItemID],
        Sending[Description],
        Sending[Item Type],
        "QT", SUM ( Sending[Quantity (all)] )
    )
VAR OnlyRec =
    ADDCOLUMNS ( EXCEPT ( receiving1, sending1 ), "From table", "Receiving" )
VAR OnlySend =
    ADDCOLUMNS ( EXCEPT ( sending1, receiving1 ), "From table", "Sending" )
RETURN
    UNION ( OnlyRec, OnlySend )