none
How to query rows that do not exist in a merge query RRS feed

  • Question

  • Hello,
    I am very new at using Power Query Editor in Excel.
    I have 2 files (data sources) that I would like to merge.

    However, there is information that is not been merged properly and I would like to know those rows so I can analyze it better.
    The Join Kind I select is "Inner".
    In the screenshot attached shows that 112,222 rows are matched out of 113,015. How could I query those exceptions (793 rows)?

    Thanks in advance


    JaimePR,

    Monday, February 11, 2019 11:33 PM

Answers

  • Hi JaimePR85

    All looks good:

    You are merging 2 tables with an Inner join. Only the rows where there is the exact same data in both columns of the joins will show up.

    PowerQuery is just telling you that only 112,222 rows match this condition out of a total of 113015 rows (and 113015 may be only a sample of your table, Power query limits its check to save ressource).

    Note that the case is important, "a" does not match with"A". Then it is always good to "clean" your columns with Text.Trim to remove unwanted space, and if the case is an issue you can use Text.Upper.

    If you want to be sure to keep all the rows from your first table, you can choose  a Left.Join instead of an Inner.Join. Then your Codigo rows with no match in CedDesc will show up with a "null" result for the merge column


    Useful links:

    Power BI – Seven Types of Table Joins

    Merge Operations in Power BI

    Merge Tables using Outer Joins in Power Query


    • Edited by anthony34 Tuesday, February 12, 2019 6:49 AM
    • Marked as answer by JaimePR85 Tuesday, February 12, 2019 3:20 PM
    Tuesday, February 12, 2019 6:40 AM

All replies

  • Hi JaimePR85

    All looks good:

    You are merging 2 tables with an Inner join. Only the rows where there is the exact same data in both columns of the joins will show up.

    PowerQuery is just telling you that only 112,222 rows match this condition out of a total of 113015 rows (and 113015 may be only a sample of your table, Power query limits its check to save ressource).

    Note that the case is important, "a" does not match with"A". Then it is always good to "clean" your columns with Text.Trim to remove unwanted space, and if the case is an issue you can use Text.Upper.

    If you want to be sure to keep all the rows from your first table, you can choose  a Left.Join instead of an Inner.Join. Then your Codigo rows with no match in CedDesc will show up with a "null" result for the merge column


    Useful links:

    Power BI – Seven Types of Table Joins

    Merge Operations in Power BI

    Merge Tables using Outer Joins in Power Query


    • Edited by anthony34 Tuesday, February 12, 2019 6:49 AM
    • Marked as answer by JaimePR85 Tuesday, February 12, 2019 3:20 PM
    Tuesday, February 12, 2019 6:40 AM
  • Thanks for your reply Anthony,

    I decided to import data to temporaly tables in MSSQL. Seems easier to me to analyze those 793 rows

    Here is more information of how to import data from Excel to MSSQL.

    https://docs.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql?view=sql-server-2017



    JaimePR,

    Tuesday, February 12, 2019 3:38 PM