Hello,
I come across a condition issue in PowerQuery.
I receive daily an XLS file containing 2 tabs with the same structure:
Tab 1: production history of the last 30 days, excluding production of the day,
Tab 2: Production of the Day
In my PQ request, I automated the compilation of the two tabs with a function. So far, no problem.
My problem occurs when the output of the day is zero: "Tab 2" does not contain any field wording which causes my request to "crash". The only value in this tab in this case is “No data available”.
In order to solve the problem, I opted to create a condition on whether or not the value "No data available" in "Tab 2" to guide the preparation of the data to the correct processing (processing of a single tab or merging of the 2 tabs).
1 - I created a list from the reprocessing of my source xls file that contains the value "No data available" if no data in "Onglet2" otherwise the field is empty.
2 – I have defined a parameter based on this list: “Parameter 1”
3 – I have integrated a processing condition into my PQ request taking into account "Parameters 1". The syntax is as follows:
-------------------------------------------------------------------------------------------------------------
let
Request1 =
if Parameters 1="No data available"
then
let
Treatment1=…(processing a single tab).
in
Treatment1
else
let
Treatment2=….(merging of the two tabs)
in
Treatment2
in
Request1
----------------------------------------------------------------------------------------------------------------------
And here I get the following message:
“Formula.Firewall: Query 1” (Query 1 step) refers to other queries or steps and therefore cannot directly access a data source. Rebuild this combination of data.”
In the parameter, when I use “All” instead of “Request”, my request works…
For information, my XLS file is stored on a SharePoint with a "Professional" access authorization (I don’t know if it’s important, we never know...)
A configuration, a subtlety escapes me? Is there a simpler solution to solving this problem?
Thank you in advance for your time studying my case.
Best regards,
