Combine datasources

[jalaomar]

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

[v-yiruan-msft · Accepted solution]

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

[jalaomar]

Hi @v-yiruan-msft, 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

[jalaomar]

Hi @v-yiruan-msft , 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

[speedramps]

Hi Jalortmar

Grouping instructions as requested .
Simply replace the SUM operation with MAX

https://docs.microsoft.com/en-us/power-query/group-by

[jalaomar]

Hi, I tried to do the steps but it's not working. What am I doing wrong?

[AlexisOlson]

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?

[speedramps]

In Power Query append the tables.
Then in Power Query group the new table by project and Date1 with max Date2.

[jalaomar]

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