Forum Discussion

Gasdetect40's avatar
Gasdetect40
Frequent Visitor
4 years ago

How to Compare 2 Spread Sheets

Scenario #1: (difficult)
We are renting assets (2 seperate assests called monitors and pumps) daily to our customers based on a reservation system that is displayed on list #1. This list has the company name, forman name, forman ID# and the amount of assets required for that day. Sometimes our staff makes errors and issues more or less than they should. List #2 is a record of what was scanned out and given to our customers, each customer is represented by Company, First Name, Last Name and a Specific ID#. 
 
I would like to be able to copy and paste each list into an online excel document and  have power BI spot the differences. Eg. If Jason wing with Heavy Crude reserved 10 monitors and 2 pumps but was only issued 8 monitors and 0 pumps I would like power BI to flag that some how.
 
Scenario #2: (easy)
I would like power BI to just count what was issued to each person (list #2) so I can see 'Heavy Crude, Jason Wing, 30 monitors, 10 pumps' 
 
P.S. - I do not want to post the list #1 publicly but will send in private if requested. 
 
Need Help

1 Reply

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Gasdetect40 

     

    According to your description, the current data is not sufficient to complete the requirement. You can mock up some dummy data in Excel to show the existing data in list #1 and list #2 and paste them in the post. This can reduce our time to reproduce the sample data. 

     

    If I understand it correctly, list #1 only has the asset types and asset quantities every company needs on a date. It only contains reservations on a date, right?

     

    In list #2, I guess every device has a unique id (is it Electronic ID or Serial No.?). But we also need a column to tell which asset type it is. At present you only have Description in the table. We don't know which one is a monitor and which one is a pump. It is suggested to prepare a table AssetList in advance. AssetList should have a list of all assets that can be rented and their asset type, asset id, asset name, .etc. 

     

    Then we need to build relationships between tables in the model. For example, 

    • AssetList (one-side) --> List #2 (many side), single filter on Asset ID column;
    • List #1 (one-side) --> List #2 (many side), single filter on Company ID (or Company Name if it is unique) column;

     

    Then we can create calculations from both tables and compare the difference. 

     

    So please first prepare some dummy data that can reply above questions and paste them in your post. Please remove sensitive data before sharing. How to provide sample data in the Power BI Forum - Microsoft Power BI Community

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.