Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sboobar
Frequent Visitor

Merge 2 Tables matching date column and Index column

I have two tables/queries in Power BI that are already matched by an similar index column, however, both tables show daily data with different columns. I'm trying to figure out how to combined these two tables so that I have daily data for each index/item and all columns. Whenever I match on index ignoring date I'm not getting the results I want. Below is an example of what I am trying to achieve.

 

TABLE 1   
DateIndexGreen 
1/1/202317 
1/2/20231545 
1/3/2023168 
1/1/2023221 
1/2/20232547 
1/3/2023285 
1/1/20233312 
1/2/202330 
1/3/202334 
    
    
TABLE 2   
DateIndexRed 
1/1/202315 
1/2/2023164 
1/3/2023112 
1/1/2023278 
1/2/2023292 
1/3/2023241 
1/1/202332 
1/2/202337 
1/3/202337 
    
GOAL:   
DateIndexGreenRed
1/1/2023175
1/2/2023154564
1/3/202316812
1/1/202322178
1/2/2023254792
1/3/202328541
1/1/202333122
1/2/2023307
1/3/2023347
1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @sboobar 

 

This is easy with the Merge Queries feature in Power Query. Merge queries overview - Power Query | Microsoft Learn

Click on "Merge Queries" to open the Merge window, hold on "Ctrl" key on keyboard and select both Date and Index columns as the matching columns in two tables. Select "Full Outer" join kind in case that some row is missing in one table. Click "OK". 

vjingzhang_0-1677130944888.png

You will have a result similar to below. A new "Table 2" column appears. 

vjingzhang_1-1677130966155.png

Click the expand icon on "Table 2" column header and select only "Red" column to expand to the table. You will have the expected result then. 

vjingzhang_2-1677131048484.png

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

 

View solution in original post

5 REPLIES 5
v-jingzhang
Community Support
Community Support

Hi @sboobar 

 

This is easy with the Merge Queries feature in Power Query. Merge queries overview - Power Query | Microsoft Learn

Click on "Merge Queries" to open the Merge window, hold on "Ctrl" key on keyboard and select both Date and Index columns as the matching columns in two tables. Select "Full Outer" join kind in case that some row is missing in one table. Click "OK". 

vjingzhang_0-1677130944888.png

You will have a result similar to below. A new "Table 2" column appears. 

vjingzhang_1-1677130966155.png

Click the expand icon on "Table 2" column header and select only "Red" column to expand to the table. You will have the expected result then. 

vjingzhang_2-1677131048484.png

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

 

Sahir_Maharaj
Super User
Super User

Hello @sboobar,

 

To achieve the goal, you need to perform a join operation between the two tables based on the "Date" and "Index" columns.


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Thank you. I tried your formula below and unfortunately couldn't get it to work. JOIN did not appear when I was typing the formula.

 

Combined = SUMX(JOIN('TABLE 1', 'TABLE 2', "Date", "Date", "Index", "Index"), [Green] + [Red])

3. Save the measure and close the formula bar.
4. Go to the report view and create a pivot table using the "Date" and "Index" columns for Rows and the "Combined" measure for Values.


This should give you the combined data you are looking for.

 

Let me know if you require further assistance.


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

1. Load both tables into the data model.
2. Right-click on one of the tables and select "New Measure".
3. Write a DAX formula to combine the data from both tables, for example:

 

Combined = SUMX(JOIN('TABLE 1', 'TABLE 2', "Date", "Date", "Index", "Index"), [Green] + [Red])

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors