Forum Discussion

dgbraqe's avatar
dgbraqe
Frequent Visitor
8 years ago
Solved

Append table lost rows

Hi, I'm having a problem loading two queries from different bases and merging them using the "Append as new" option. The resulting table is joining records incorrectly, following examples:   Base 1...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

dgbraqe,

Create a new append  table using DAX below and you should get correct distinct values.

Table = UNION('BASE 1','BASE 2')

Regards,
Lydia

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    8 years ago
    Anonymous wrote:

    dgbraqe,

    Create a new append  table using DAX below and you should get correct distinct values.

    Table = UNION('BASE 1','BASE 2')

    Regards,
    Lydia

    Isn't that a workaround? Why isn't it working correctly, or at least, why aren't we understanding it correctly, in the M code?

  • dgbraqe's avatar
    dgbraqe
    Frequent Visitor
    8 years ago

    edhans, perfect your observation.

     

    When we use the DAX function, the problem does not occur.
    This shows even more that we have a problem with the M (Power Query) language, I understand that the use of "Table.combine" (M language) or "UNION" (DAX language) should have the same result.

    I opened a ticket with Microsoft, I will inform you as soon as I have a definitive answer.

     

    Anonymous, please, leave the topic open until we understand the reason for the difference between the M and DAX language.

      • Ehren's avatar
        Ehren
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        dgbraqe, I've tried reproducing this locally (using SQL data refreshed via a Gateway) and am unfortunately not seeing any duplication. Would it be possible for you to try the following?

         

        • Try duplicating the query that does the Table.Combine operation, and then buffering the result (i.e. add a step at the end that does = Table.Buffer(previousStep)). Does this version of the query exhibit the same behavior?
        • Try duplicating the query that does the Table.Combine operation, and buffering each individual table being combined (e.g. = Table.Combine({Table.Buffer(Query1), Table.Buffer(Query2)})). Does this version of the query exhibit the same behavior?
        • Try duplicating the query that does the Table.Combine operation and doing a group by and count of the unique field you're seeing duplicated, then filter for Count > 0 (which should return an empty table when run locally in PBIDesktop). When refreshed via the gateway, does this query produce any results?

        Also: what version of SQL Server are you pulling from?

         

        Thanks for your help.

        Ehren