Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
D_PBI
Post Patron
Post Patron

Power Query - How to force Query 2 to complete its processing before Query 1 starts its processing?

Hi,

 

I have two Queries (Query 1 and Query 2) that, separately, each extract data and perform some Applied Steps. However, Query 1 merges (left join) with Query 2.

I would like to know if there is any method, in Power Query or something other way, to force Query 2 to fully complete its processing (complete its Applied Steps) before Query 1 starts it processing (Applied Steps)?

This way I can be assured that what is brought into Query 1 (via the merge/left join) is the full data from Query 2, plus I can eliminate any circular dependancies too.

 

If you know of a way then please share??

Thanks in advance.

1 ACCEPTED SOLUTION

Thanks @Anonymous 

I had read the links you provided before I started this thread but didn't feel they would solve my particular issue, partly because two of the links mentioned gateways which I don't have in my setup. However, I have solved the problem which I'll explain below in case it helps others.

 

A lot of the links state to set the report's Privacy level to 'None' so I did in both the Desktop and Service source configurations. The error still occurred. Reviewing this I focused on configuring the Privacy setting to 'Organizational' in both the Desktop and Service - and it worked!  The refreshes then became successful. Tinkering further, configuring the Privacy setting to 'None' in the Desktop but 'Organizational' in the Service - it still worked!  So it would seem that you only need to focus on configuring the Service Privacy setting to 'Organizational' for it to work. I guess the Desktop Privacy setting is only relevant during the development phase.

Does anyone know why the Privacy setting needs to be 'Organizational' in the Service?  Is it a must for when connecting to Dynamics 365?  Is it due to how my company's Office 365/Power BI Service has been configured?


I've awarded you a kudos as you have re-nudged me towards to the link that helped.
Thanks.

View solution in original post

5 REPLIES 5
edhans
Super User
Super User

If Query 1 does a left join with Query 2, then PQ has to processes Q1 first to know what records it needs from Q2, then it processes Q2 and brings in the relevant records. For an extreme example, if Q1 has 10 records and Q2 has 1,000,000 records, you are only interested in the 10 from Q1 to merge with the relevant records from Q2, which may be more than 10 if it is a one-to-many relationship.

 

What circular dependency errors are you getting? A simple left join won't cause that, and having one query processes in a different order won't prevent it.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

Hi @D_PBI  & @edhans 

 

a small addition to what was posted by @edhans.

 

Technically, if we are talking about PBI Desktop/Service, Query 1 and Query 2 will be evaluated in separate threads. Meaning that Query 1 is not guaranteed to use Query 2 outputs - actually, PBI incorporates Query 2 code into Query 1 code and run this big query, in some occasions it can use cached Query 2 result, but it does not have to.

 

This mean:

(1) there is no guarantee to the sequence of the queries - they more or less run simultaneously;

(2) on odd occasions, it is possible to have Query 2 result and what was merged into Query 1 be different - in my testing I was pinging MS Flow that returns utcNow to PBI and got 1 sec variance between Query 2 result and Query 2 data merged into Query 1.

 

In most scenarios, it may be not a great deal, but in extremely real-time data processing it may. It is not a problem for PBI Datalows, can be conditionally fixed in PBI Desktop, but there seems to be no cure for PBI Service.

 

For more details, please check Chris Webb's blog: https://blog.crossjoin.co.uk/2019/03/26/power-bi-caching-parallelism-and-power-query-refresh-perform...

 

Kind regards,

JB

Thank you @edhans and @Anonymous for your replies.

The actual error message I'm receiving is below:

Message:Information is needed in order to combine data Table: Agreements Table.

 

The 'Agreements' table (i.e. Query 1) is the table that performs a LEFT JOIN with the second table (Query 2). I do not know the cause of the above error message, but I have narrowed it down to the actual LEFT JOIN Applied Step that is causing the error.
When I refresh the whole dataset (the entire Power BI report) from within PQ or PBI the report refreshes fine, and no errors are received. However, it is only when I refresh the Power BI report from the Power BI Service that it fails and the above error message is received. Why does it refresh in the Desktop but not in the Service??

It isn't just happening in one report. It is happening in two others that have a similar Data-Model (i.e. Applied Steps with joins). These other two reports are failing on the joining Applied Steps too. Therefore, I did wonder if the failures were due to circular references (I have received explicit circular reference errors in the past so I know those error messages can be specific in the error message) - maybe the error are due to circular references, although you guys don't think so and also why would it refresh in Desktop if there was a circular reference problem?!

I'm now thinking the error is due to the connection used to retrieve each table that comprises the Data-Model. I'm using the Power Query Builder (from the XRMToolBox) third-party tool.
Maybe it is the connection code produced by the Power Query Builder that is preventing the report from refreshing in the Power BI Service. Below is the opening code snippet from a table that is extracted from Dynamics 365 using Power Query Builder:
XRM.PNG

 

There is an alias/variable if you like named ServiceRootURL. This is assigned the relative path elsewhere in Power Query, within the same report, so the above code snippet picks up the relative path. If you were to connect to Dynamics 365 using, say, the Dynamics 365 Online connection, it would be the same relative path you insert into that connection.
Could it be the above Power Query Builder connection code is not able to refresh in the Power BI Service, thus causing the error, and why?

If you (anyone) can help with this then it will be much appreciated?
Thanks.

Anonymous
Not applicable

Hi @D_PBI ,

 

Power BI Service is sometimes quite different to PBI Desktop. In my personal experience, e.g.,  #shared did not work in PBI Service as it did on the Desktop platform.

 

(1) Can you check that both queries load data (without joining) without any problem in Power BI Service? 

 

(2) Have you checked these:

https://community.powerbi.com/t5/Service/Refresh-error-message-Information-is-needed-in-order-to-com...

https://community.powerbi.com/t5/Service/Information-is-needed-in-order-to-combine-data/td-p/574058

https://community.powerbi.com/t5/Service/Information-is-needed-in-order-to-combine-data-Table-withou...

 

There are some other solutions on the "Information is needed in order to combine data" search on the forum 😁

 

Kind regards,

JB

Thanks @Anonymous 

I had read the links you provided before I started this thread but didn't feel they would solve my particular issue, partly because two of the links mentioned gateways which I don't have in my setup. However, I have solved the problem which I'll explain below in case it helps others.

 

A lot of the links state to set the report's Privacy level to 'None' so I did in both the Desktop and Service source configurations. The error still occurred. Reviewing this I focused on configuring the Privacy setting to 'Organizational' in both the Desktop and Service - and it worked!  The refreshes then became successful. Tinkering further, configuring the Privacy setting to 'None' in the Desktop but 'Organizational' in the Service - it still worked!  So it would seem that you only need to focus on configuring the Service Privacy setting to 'Organizational' for it to work. I guess the Desktop Privacy setting is only relevant during the development phase.

Does anyone know why the Privacy setting needs to be 'Organizational' in the Service?  Is it a must for when connecting to Dynamics 365?  Is it due to how my company's Office 365/Power BI Service has been configured?


I've awarded you a kudos as you have re-nudged me towards to the link that helped.
Thanks.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors