merged queries in power BI desktop

[PBILover]

Hi,

I have one Cosmos main table, old Master data  for old clients in SQL , and new master data  table for new clients  from cosmos.

In the main table , there is one field named as abc, if abc is null then i am taking  client data from SQL  else from cosmos.

Ciurrently i am achieving this by using Merged query in poweer bI desktop but looks like it is affecting data loadperformance .

Is there any way to achieve this  without merging queries.

Thank you

[v-diye-msft]

Hi @PBILover 

If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly. If not, please kindly elaborate more. thanks!

[PBILover]

HI @v-diye-msft 

 Sadly not, Currently i am using the merging at MQuery as i was doing previously but my observation is that sometimes it is taking more time to data refresh, and if the merging columns are having duplicates then it will go in infinite loop.

We can merge the columns by using Dax (by using related or naturalleftJoin function) but i am not sure about performance,

Thank you,

Namita

[amitchandak]

@PBILover , Power query is a good place to do that. Merge and Append are two options. When you say old, does data get refreshed full every time from both databases?

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

See if you can configure incremental on Database.

Refer this, if it can help in improving performance

https://www.thebiccountant.com/2016/11/08/speed-powerbi-power-query-design-process/

[PBILover]

Thanks Amit.

In my scenario, i need to use merge query and which is causing performance issue.

yeah everytime i am refreshing data it does refreshed data in both databases.

I am trying to implement the incremental data refresh on main table , but for only 14,00000 data it is stuck on evaluating mode for more than 1 hour so i have to kill it.

[mahoneypat]

If the datasets contain the same columns, it will be much faster to append them.  From there, you can remove duplicates, filter nulls, or do more advanced logic if needed.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[PBILover]

Thanks for your reply Pat.

Unfortunately no. As main table is having transactional data while other tables are Master Tables.

Thanks