Merging 2 Tables

[maurom]

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!!!!

[v-qiuyu-msft]

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:

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

Best Regards,
Qiuyun Yu

[Greg_Deckler]

@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

[Neuro81]

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

[MattAllington]

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.