Need Help with Creating Table from Four Different Datasets

[cyborgandy]

I have 4 different excel dataset connected to power bi ,below are the example of different dataset. All are in different excel sheets and connected through sharepoint folder

Month	Rail Spend	Cost Centre1	Product Area
Sep	11.7	ABCD	Rail
Sep	11.7	ABCD	Rail
Oct	11.7	ABCD	Rail
Oct	11.7	ABCD	Rail
Month	Hotel Spend	Cost Centre	Product Area
Sep	135	ABAD	Hotel
Sep	135	ABAD	Hotel
Oct	85.5	ABAD	Hotel
Oct	97	ABAD	Hotel
Month	Confrence Spend	Cost Centre 1	Product Area
Sep	150	EEEE	Conference
Sep	1000	EEEE	Conference
Oct	250E	EEEE	Conference
Oct	250	EEEE	Conference
Oct	720	EEEE	Conference
Travel Month	Ticket Number	Air Spend	Cost Centre1	Product Area
Sep	12345	119.96	YYYY	Air
Sep	56789	151.96	YYTY	Air
Oct	22345	279.96	YYAB	Air
Oct	34556	70.48	YYYY	Air
Oct	22334	161.98	YYCA	Air

I need to build below table in power bi,could someone help if it is possible as the value is in different datasets

Product Area	Sep Spend	Oct Spend
Air	XXXX	XXXX
Confrence	XXXX	XXXX
Rail	XXXX	XXXX
Hotel	XXXX	XXXX

[tamerj1]

Hi @cyborgandy 

what is the data type of the month columns?

you can have a date table or simply a month table that filters the four tables 1-* 

the rest is just 4 simple measures (one for each metric table) like SUM ( 'Table'[Spend Column] )

use a matrix visual, place the month column from the dimension table at the columns of the matrix and activate the 'Values on rows" option

[cyborgandy]

@tamerj1 , the month column is in text in all the four dataset..

[tamerj1 · Accepted solution]

@cyborgandy 
Please refer to attched sample file and the following screenshots

[tamerj1]

@cyborgandy 

You need to create one to many relationships between the dimension table and the four tables.