Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Compare column from Both the tables

Jump to Best Answer
  • 1.  Compare column from Both the tables

    Posted Aug 13, 2019 12:48 PM

    Hi Team,
     I have two tables like one is for the month of may and another one is for the month of June,

    in these two tables I have one common column "category"  ,
    Now I want to show categorywise difference in one table






    I want output table like this but one more row needed to show difference for each category
    With the help of above two tables I want to display unmatched records from Both the tables in one Table.
    Any Suggestion Please help me

    Thanks in Advance

     



    ------------------------------
    Reddy
    ------------------------------


  • 2.  RE: Compare column from Both the tables
    Best Answer

    Gold Contributor
    Posted Aug 15, 2019 06:58 AM
    Hi @Aravind Reddy

    I would suggest you to append both tables in Power Query to have one table containing both months.

    Secondly, best practice is to create a date table from which you create a one-to-many relationship with your dataset table.

    Thirdly, make sure to identify the date column in your calendar table as "Mark as Date Table" in the Modeling Tab of the Ribbon

    Then you will be able to use the Time Intelligence functions available in Power BI.
    Check out Quick Measures feature to help you construct your DAX formula
    Click on the 3 dots on the right side of the table name and choose "New Quick Measure"

    Hope that helps.
    Let us know how it has worked for you



    ------------------------------
    David Gerard
    Business Intelligence Consultant
    Kampala
    ------------------------------



  • 3.  RE: Compare column from Both the tables

    Posted Aug 15, 2019 04:50 PM
      |   view attached

    Hello Aravind Reddy,

     

    I am providing a 'single point' PowerBI solution (.pbix) that utilizing your May and June data displays

    1. Rows (based on category) ONLY in the May table. (That is, rows in the May table and NOT in the June table.)
    2. Rows (based on category) ONLY in the June table. (That is, rows in the June table and NOT in the May table.)
    3. Rows (based on category) in BOTH the May and June tables.

    I provide details on the solution below but first wanted to suggest some other ideas.

    By referring to this as a 'single point' solution, I mean to say it is specific to your May and June data and does not account for any previous or future month comparisons. For example, Comparing June to July, July to August and so on.

    In fact, I suggest you may want to consider preparing your monthly data creating all needed data points prior to importing into PowerBI. You could then focus on utilizing PowerBI for visualization and using DAX for any measures to help with analyzing your data. In short, even though this can be done in PowerBI (using the workflow approach of M), a better practice would be to prepare your data outside of PowerBI.

    If your data resides in relational tables you could develop SQL to perform a monthly comparison between tables and report as needed. If your data resides in Excel you can achieve similar results using vlookup (or Hlookup depending on your row/column orientation).

     

    INorNOT.pbix performs the following steps:

    1. Load May data.
    2. Load June data.
    3. Duplicate May table.
    4. Rename this duplicate of May table to INMayONLY.
    5. Duplicate June table.
    6. Rename this duplicate of June table to INJuneONLY.
    7. Duplicate May table.
    8. Rename this duplicate of May table to InBoth.
    9. Hightlight INMayONLY and merge queries as:

    This is akin to an SQL 'Left Join' (also known as 'Left Outer Join').

     

    1. Highlight INJuneONLY and merge queries as:
    2. Highlight InBoth and merge queries as:

    This is akin to SQL 'inner join'.

    I hope this helps.

    Hello Aravind Reddy,

     

    I am providing a 'single point' PowerBI solution (.pbix) that utilizing your May and June data displays

    1. Rows (based on category) ONLY in the May table. (That is, rows in the May table and NOT in the June table.)
    2. Rows (based on category) ONLY in the June table. (That is, rows in the June table and NOT in the May table.)
    3. Rows (based on category) in BOTH the May and June tables.

    I provide details on the solution below but first wanted to suggest some other ideas.

    By referring to this as a 'single point' solution, I mean to say it is specific to your May and June data and does not account for any previous or future month comparisons. For example, Comparing June to July, July to August and so on.

    In fact, I suggest you may want to consider preparing your monthly data creating all needed data points prior to importing into PowerBI. You could then focus on utilizing PowerBI for visualization and using DAX for any measures to help with analyzing your data. In short, even though this can be done in PowerBI (using the workflow approach of M), a better practice would be to prepare your data outside of PowerBI.

    If your data resides in relational tables you could develop SQL to perform a monthly comparison between tables and report as needed. If your data resides in Excel you can achieve similar results using vlookup (or Hlookup depending on your row/column orientation).

     

    INorNOT.pbix performs the following steps:

    1. Load May data.
    2. Load June data.
    3. Duplicate May table.
    4. Rename this duplicate of May table to INMayONLY.
    5. Duplicate June table.
    6. Rename this duplicate of June table to INJuneONLY.
    7. Duplicate May table.
    8. Rename this duplicate of May table to InBoth.
    9. Hightlight INMayONLY and merge queries as:

    This is akin to an SQL 'Left Join' (also known as 'Left Outer Join').

     

    1. Highlight INJuneONLY and merge queries as:
    2. Highlight InBoth and merge queries as:

    This is akin to SQL 'inner join'.

    I hope this helps.



    ------------------------------
    Steven Wentworth
    SyncraTec Solutions, LLC
    Yardley PA
    215-310-1750
    ------------------------------

    Attachment(s)

    zip
    CodeAndData.zip   137K 1 version


  • 4.  RE: Compare column from Both the tables

    Bronze Contributor
    Posted 30 days ago
    Hello,
    May be this solution will help :
    https://thebipower.fr/index.php/2019/03/08/compare-the-same-data-from-two-columns-in-power-query/
    Regards

    ------------------------------
    Didier Terrien
    Group Data Quality Officer
    ------------------------------