Forum Discussion
Combine datasources
Hi all,
Is it possible to combine different datasources that have information sitting in different datasources for the same project.
please see attached screenshot of the outcome I am looking for.
Project A has Date1 in Table1
Project A has Date2 in Table2
How can combine these information in one single table?
BR
J
Hi jalaomar ,
You can create a calculated column as below to get the Date2 and check whether that is what you want...
NDate2 = VAR _date2 = CALCULATE ( MAX ( 'Table2'[Date2] ), FILTER ( 'Table2', 'Table2'[Project] = 'Table1'[Project] && 'Table2'[Date2] >= 'Table1'[Date1] ) ) RETURN IF ( ISBLANK ( 'Table1'[Date2] ), _date2, 'Table1'[Date2] )If the above one is not working for your scenario, please provide more sample data with Text format and your expected result included with backend logic and special examples. Thank you.
Best Regards
8 Replies
In Power Query append the tables.
Then in Power Query group the new table by project and Date1 with max Date2.Hi speedramps , Thanks! I know Append query but never done this grouping. would you like to show from a screenshot how that is done?
BR
J
How do you know that the green date belongs in that row?
If you had another row in the blue table,
A 2021-05-31 (blank)
would the green date pair with this row, the top row, or both?
Hi Jalortmar
Grouping instructions as requested .
Simply replace the SUM operation with MAXHi, I tried to do the steps but it's not working. What am I doing wrong?
Hi jalaomar ,
You can create a calculated column as below to get the Date2 and check whether that is what you want...
NDate2 = VAR _date2 = CALCULATE ( MAX ( 'Table2'[Date2] ), FILTER ( 'Table2', 'Table2'[Project] = 'Table1'[Project] && 'Table2'[Date2] >= 'Table1'[Date1] ) ) RETURN IF ( ISBLANK ( 'Table1'[Date2] ), _date2, 'Table1'[Date2] )If the above one is not working for your scenario, please provide more sample data with Text format and your expected result included with backend logic and special examples. Thank you.
Best Regards
Hi Anonymous , Thank you! Will try it out today. Just a question, will this calculated column work if I append the two tables?
because i need to append to be able to have all the ID's in one table.
BR
J
Hi Anonymous, I could not make it work with my appended query.
Below you can see that I have 3 tabels with source data and i want all information for all projects to be in the same table, hence I need to append them (due to KPI calculation)
Could you maybe support?
GPR Data
Project ID Baseline Actual A 2021-12-23 B 2021-11-02 C 2021-10-14 2021-10-14 D 2021-04-23 2021-04-20 Project File Data
Project ID Baseline Actual E 2021-11-12 2021-11-13 F 2021-12-23 PV Data
Project ID Baseline Actual A 2021-12-25 B 2021-12-27 F 2021-12-30 Expected Outcome (Append the 3 above tavles as new query in Power BI)
Project Performance
Project ID Baseline Actual A 2021-12-23 2021-12-25 B 2021-11-02 2021-12-27 C 2021-10-14 2021-10-14 D 2021-04-23 2021-04-20 E 2021-11-12 2021-11-13 F 2021-12-23 2021-12-30
