Forum Discussion

falsumconsult's avatar
falsumconsult
Regular Visitor
4 years ago

Identify Errors in Table 1 with reference data from Table 2

I am very new to data analysis and Power BI. So, please excuse me if this is an easy answer!

 

Background

I have payment data from a third-party with 1000 rows of payment data in 'Table 1'. Column C holds data with an 8-digit classification for the type of payment made.

 

'Table 2' is a small list of '8-digit' expected values for the type of payment made. 'Table 2, Col A' contains only 10 expected values.

 

When compared, any data point in 'Table 1, Col C' that is outside of the 'Table 2' reference data is deemed to be 'errata' with further investigation needed.

 

Request

I am looking to understand:

  1. How I can identify rows in 'Table 1' that are deemed to be 'errata'. 
    1. Would this mean pulling these 'errata' rows into a new table?
    2. Or, am I able to do this without generating new tables?
  2. Run a high-level metric which displays % of total payments with errata.

 

Any help & guidance would be greatly appreciated as I learn my way around Power BI and Data Analysis.

Need Help

5 Replies

  • jaideepnema's avatar
    jaideepnema
    Solution Sage
    4 years ago

    Hi falsumconsult ,

    There are many ways of doing this. You can import both the tables into Power BI and create a relationship basis the 8 digit key which as per understanding will create a many to one relationship with the table 1 on the many side.  Then you can use DAX Measures to calculate the percentage of total payments with errata. You will get a blank value whenever a matching value is not found.

     

    Alternatively you can merge these two tables into Power BI Query editor to get the required column into your 1st table and then write measures to the final calculation required. Hope this helps !!

     

    Please accept this as a solution if your question has been answered !!

    Appreciate a Kudos ğŸ˜€

    Connect with me on LinkedIn: https://www.linkedin.com/in/jaideepnema/

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    Create a 1-to-many relationship from Table2 Column A to Table 1 Column C. Put the data columns you need from Table 1 into a table visual and apply a filter so that you only show values where Table 2 Column A is blank.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi falsumconsult ,

     

    You could create two measures to get the result.

    Measure1 is to add marker.

    Measure1 = IF(SELECTEDVALUE(table1[columnC]) in VALUES(table2[columnA]),"not errata","errata")

    Measure2 is to calculate the percentage.

    Measure2 = 
    var _total = calculate(sum([value]),allselected('table1'))
    var _errata = calculate(sum([value]),filter(allselected('table1'),Measure1="errata"))
    return
    _errata/_total

     

    Best Regards,

    Jay

    • falsumconsult's avatar
      falsumconsult
      Regular Visitor
      4 years ago

      Hi Jay,

      This is great and a real help. The only issue I have is that measure 2 is returning a string value. Can you please advise on how I convert to a %?

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi falsumconsult ,

         

        What's data type of the [values]? _total  and _errata sum of corresponding value. They should be number type.

        Try modifying '_errata/_total' to divide(_errata,_total).

        If you are saying the format, you could select the measure, go to Data View, modelling Tab, Change the data type to percentage.

         

        Best Regards,

        Jay