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
maurom
New Member

Merging 2 Tables

Hello,

 

I am new to Power BI and I am starting to learn its ways... I have what maybe a basic question but after searching I have not been able to fin the answer:

 

I have two tables that I want to merge in the following way:

 

Table 1

Date 1

Number 1

03/01/2016

            10

05/02/2016

            11

 

Table 2

Date 2

Number 2

03/05/2016

               5

05/04/2016

              10

 

What I want:

 

Year

    Month    

   Number 1 Tabla 1    

   Number 2 Tabla 2

2016

   March

               10

                5

2016

     May

               11

              10

 

 

Thank you!!!!

4 REPLIES 4
v-qiuyu-msft
Community Support
Community Support

Hi @maurom,

 

In your scenario, you can add two custom columns(Month, Year) in Table1 and Table2. Then use Lookupvalue() function to get number2 from Table2. Please refer to screenshots below:

 

Column = LOOKUPVALUE(Table2[Number 2],Table2[Month],Table1[Month],Table2[Year],2016)z2.PNG

 

 

If you have any question, please feel free to ask.

 

Best Regards,
Qiuyun Yu

 

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greg_Deckler
Super User
Super User

@maurom - If you want it all done in a single query, you can refer to my blog article on this subject here:

 

http://social.technet.microsoft.com/wiki/contents/articles/32915.power-bi-merge-query-with-m.aspx

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Neuro81
Helper I
Helper I

okay so a bit of scrubbing and then merging the data sets
if your just want to get the month names you have a couple of choices personally id do this in dax so that you still have the original dates but if you dont care about that then highlight the date columns and
Click Transform
Click Date
click Month
and Choose start of Month

now go back to Table 1 and on the home tab click Merge
highlight the date column in each table (make sure you have table 1 and table 2 in the display)
you can choose inner join since the dates both match but if you dont then to be on the safe side choose Full Outer join

a new column should appear (it says table for all the rows), in the header click expand
deslect Date 2 and deselect use original name
and thats it!

hope that helps

 

-- ------------------------------ --
Please mark solutions as complete when your question has been answered
-- ------------------------------ --

This can be done, but it may not be optimal. Power BI allows you to keep the detail in your data model (Ie day level data) and still create a report like you have shown (by month).  This has the benefit of allowing further analysis, like day level patterns etc. Also your design is "unpivoting data" into multiple columns, and this also is probably not optimum. 

 

My knowledge base has info about data shape and calendar tables - both which are part of a good data model design. 

 

http://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/

 

It may be worth reading up before you start to understand good practices. Hope that helps. 

 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors