cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gpp007
New Member

merge two query

Hello All , 

So i am having some trouble merging two tables in power bi 

 

fist query consist of portfolio that i receve every quarter with 4 columns (Date, name of ptf, name of stock, quantity of stock) and i might have couple of differente porfolio that are not changing at same days their stock quantity. 

Second query is a price table, i have an extract of all stock price for all days of 2020 with 3 columns (Date, name of stock, price). 

 

My objective is to get the name of ptf (and there is more than one), name of stock, quantity, and price  on the same query in order to calculate weight of position at any days during the year. 

 

Hope you can help me 

 

Thanks a lot

 

 

Capture.PNG

Capture2.PNG

Capture3.PNG

1 ACCEPTED SOLUTION
nandic
Memorable Member
Memorable Member

Ideally it would be to have Query2 (historical data) for each date. Query1 (portfolio) may not be on each date, but for each date where it occurs, the same combination (stock, date) should be available in history table. This way all data will be matched.
And in this case you might use Inner Join to return only matched data. If you need all data (even without join) you can use Outer join, but then there might be lots of nulls.

View solution in original post

3 REPLIES 3
nandic
Memorable Member
Memorable Member

Hi @gpp007 ,

Below is screenshot how merge query definition should look like.
If main fields are "Date" and "Stock Name", you need to merge queries using these two fields.

Note: pay attention on the order you select it (blue circles on image). Date is selected second (2), Stock name is selected first (1).
Also pay attention to Join Kind, here you define what is main table, would you like inner join, left join etc.

 

merge queries.PNG

 

Regards,
Nemanja Andic

hello 

thanks it's partly working, i have joined by right outer so i got all my calendar days in my query. 

But i can't manage to have my which i only have couple point of data to match the day of the second query where i have all date of a year. 

how should i process do you think ? 

nandic
Memorable Member
Memorable Member

Ideally it would be to have Query2 (historical data) for each date. Query1 (portfolio) may not be on each date, but for each date where it occurs, the same combination (stock, date) should be available in history table. This way all data will be matched.
And in this case you might use Inner Join to return only matched data. If you need all data (even without join) you can use Outer join, but then there might be lots of nulls.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.