How to merge rows from 2 data tables without duplication without matching key

Toral Patel 0 Reputation points
2023-10-20T14:45:05.6233333+00:00

I am using Excel PowerPivot. I have a database with all the records. In PowerPivot I have connected to the database and have added a query select * from table1. Now the problem is table1 is having huge data and daily I do data refresh in excel sheet from Data->Refresh and because not there are records in millions it is taking nearly 2 hours to fetch data. So to reduce the processing time I can add one more query to PowerPivot i.e select * from table1 where date - interval '30 day'. And daily I will do data refresh for this query which pulls data for last 30 days. So now I have 2 data models and in excel sheet I want to see the records from both the tables in PivotTable without duplication of rows. How can I achieve this?

Microsoft 365 and Office Excel For business Windows
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. AllenXu-MSFT 24,941 Reputation points Moderator
    2023-10-23T06:20:28.33+00:00

    Hi @Toral Patel,

    To merge rows from two data tables without duplication without matching key in PowerPivot, you can use the "Append Queries" feature. First, create a connection to each table in PowerPivot. Then, go to the "Power Query Editor" and select the first table. From the "Combine" dropdown, select "Append Queries". In the "Append Queries" dialog, select the second table and click "OK". This will append the rows from the second table to the first table without creating duplicates. Finally, load the combined query into PowerPivot and create a PivotTable from it.


    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.