merge two query

[gpp007]

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

[gpp007]

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

[nandic]

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.

Regards,
Nemanja Andic

[gpp007]

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 · Accepted solution]

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.