Forum Discussion
skip query step if condition is met
Hi masters,
is it possible to skip a whole query step (ex below: Combined_Exp_) if a condition is met. The query "Combined_Exp_" is basically loading data from a file that will be merged further in a another query.
However, I dont want to load this step if a list is not null.
The step to be skipped is the following:
let
Source = Csv.Document(File.Contents("C:\Mydocuments\Test.csv"),[Delimiter=",", Columns=83, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", Int64.Type}, {"Code", Int64.Type}, {"Value", Int64.Type}})
in
#"Changed Type"
Thanks for your help
Thanks for the help. I found the solution I was looking for. The script should look like the following:
let
Source =
if List.Single(DateList) = null then
Excel.Workbook(File.Contents(""C:\Mydocuments\Test.csv"), null, true)
else
null, // Preventing further steps if Source is null
Output =
if Source = null then
#table({}, {}) // Returns an empty table
else
let
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", Int64.Type}, {"Code", Int64.Type}, {"Value", Int64.Type}})in
#"Changed Type"in
Output
10 Replies
Hi Phil_Oliveira, it is possible.
add another new step at the end of your Combined_Exp_ query. Something like this:
= if List.IsEmpty(YourList) then #table(null, {}) else #"Changed Type"or (but this one does not make sense to me because list can not be null...)
= if YourList is null then #table(null, {}) else #"Changed Type"Thanks for the help. I am new with power query so I am not sure if I got it right. Should the query look something like this:
let
Source = Csv.Document(File.Contents("C:\Mydocuments\Test.csv"),[Delimiter=",", Columns=83, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", Int64.Type}, {"Code", Int64.Type}, {"Value", Int64.Type}}),#"Test" = if not List.IsEmpty(DateList) then #table(null, {}) else #"Changed Type"
in
#"Changed Type"let
Source = Csv.Document(File.Contents("C:\Mydocuments\Test.csv"),[Delimiter=",", Columns=83, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", Int64.Type}, {"Code", Int64.Type}, {"Value", Int64.Type}}),#"Test" = if not List.IsEmpty(DateList) then #table(null, {}) else #"Changed Type"
in
#"Test"
You cannot skip a step but you can present a different result. if the list is not null then present a table with the same columns but zero rows. If the list is null ingest the file.
Hi, if you woundn't want to create this table in your window, another way could probably be to create a custom function and then directly referencing the table in the function through the code mentioned below by you. It'll directly pick up the table and perform on the conditions. Thanks!
Hi Phil_Oliveira ,
- Power Query cannot fully skip a step, but you can return an empty table instead of loading data.
- This approach ensures that your query does not break while meeting your requirements.
Reference: List.IsEmpty - PowerQuery M | Microsoft Learn
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Phil_Oliveira ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi Phil_Oliveira ,
Has your issue been resolved, or do you require any further information? Your feedback is valuable to us. If the solution was effective, please mark it as 'Accepted Solution' to assist other community members experiencing the same issue.
Hi Phil_Oliveira ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution we provided for your issue worked for you or let us know if you need any further assistance?
Your feedback is important to us, Looking forward to your response.
Thanks for the help. I found the solution I was looking for. The script should look like the following:
let
Source =
if List.Single(DateList) = null then
Excel.Workbook(File.Contents(""C:\Mydocuments\Test.csv"), null, true)
else
null, // Preventing further steps if Source is null
Output =
if Source = null then
#table({}, {}) // Returns an empty table
else
let
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", Int64.Type}, {"Code", Int64.Type}, {"Value", Int64.Type}})in
#"Changed Type"in
Output
