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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
abhishekdas72
Frequent Visitor

combining 4 data sources

Hello All,

I have 4 files 
1. Open Orders (only data from Aug Sep Oct)
2.Current Month (only Aug data)
3. Completed Transaction (Jan-July data)
4.forecast(Oct Nov Dec)

all 4 above files have part & site as the key , month & spend. I did a one way cross filter connection but the calculation was not correct. so did cross filter in Both directions and many to many cardinality.
all these files are interconnected now but i want to show the 'SPEND' month wise in a single table as below

 

abhishekdas72_0-1691684485753.png

as current month is Aug it should be coming from 2 files Current month + Open PO

all files have moth column and i have also created a date table but the problem is if i link date table the relationship is not active as it shows an active relationship with other tables. Numbers refresh every week on monday as they update once the PO is received. if i connect Completed transaction table to date table it shows only Jan-july and like wise i need to see spend of all files month wise as stated above. Please assist
Merging all into one will not be an option as numbers wont be refreshed.

4 REPLIES 4
Elena_Kalina
Solution Sage
Solution Sage

Hi @abhishekdas72 

You should review this article Append queries - Power Query | Microsoft Learn and append all your tables using Power Query, then create a calendar table with columns: Date, Month, Month Number, Year, etc.

Relate it to your combined table using the "Month" field.

Set a one-directional relationship (from the date table to the merged table). Then, create a dynamic measure to handle the special logic for August.

Sorry my bad, Months were stored in single column, appending will work

 

but appending might not work if months are stored as columns
if they are date column then append might work

Vijay_Chethan
Super User
Super User

 Hello abhishekdas72,
you can append the tables in query editor based on site and part columns

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.