skip query step if condition is met

[Phil_Oliveira]

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

[V-yubandi-msft]

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. 

[Phil_Oliveira · Accepted solution]

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

[V-yubandi-msft]

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.

[V-yubandi-msft]

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.

[V-yubandi-msft]

Hi @Phil_Oliveira ,

1.   Power Query cannot fully skip a step, but you can return an empty table instead of loading data.
2.   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.

[SundarRaj]

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!

[lbendlin]

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.

[dufoq3]

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"

[Phil_Oliveira]

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"

[dufoq3]

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"