Forum Discussion
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
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
Hello abhishekdas72,
you can append the tables in query editor based on site and part columnsYou 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.
but appending might not work if months are stored as columns
if they are date column then append might workSorry my bad, Months were stored in single column, appending will work
