Conditionally Skip an Applied Step

[Anonymous]

I have a table that I want to output 4 columns sometimes and 5 columns other times.  I want to write something that will conditionally skip a merge statement in the advanced editor.  Is this possible?  Alternatively, I'm looking for a way to avoid having my power queries refresh under these same conditions.  I am aware of the query property that allows you to Turn Off Refresh All option; however, I want the end users to be able to hit Refresh All and have the data refresh when appropriate, so this is not an ideal solution for me.  This is in Excel

[spinfuzer]

You can do an if condition then Table.NestedJoin .... else then PriorStep

Or you can do a

try Table.NestedJoin(.....) otherwise PriorStep

if you want it to try to merge and if it fails then do something else.

[Brett007]

This worked for me in the Advanced Editor:
I am using it to reduce the dataset while I edit the Formulas.  It makes the tables smaller so that the formulas load faster.  I added it right after the source on each of my tables.  It references a manually entered table that I edit the amount of rows I would like to show.  If I set that table to 0 it shows all the rows in the table. Pretty neat. 

ReduceDatasetFIlter = if Table.FirstValue(ReferenceTable) = 0 then Source else Table.LastN(Source, Table.FirstValue(ReferenceTable)),

[Anonymous]

I believe I tried that .. could you provide an example or the syntax?  I don't think you can just write a contitional like that straight in the advanced editor.  It's not just one step that I'm trying to skip.  I'm trying to skip 5-6 steps that transform that column after it is merged too.

[spinfuzer · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    choice1 = 
        let
            next1 = Table.AddColumn(ChangedType, "Column2", each [Column1] + 1),
            final1 = Table.AddIndexColumn(next1, "Index" )
        in
            final1,
    choice2 = 
        let
            next2 = Table.AddColumn(ChangedType, "Column2", each [Column1] + 5),
            final2 = Table.NestedJoin(next2, "Column1", next2, "Column1", "NestedJoin" )
        in
            final2,
    conditional_stmt = if Time.Second(DateTime.LocalNow()) > 30 then choice1 else choice2 

in
    conditional_stmt

[Anonymous]

This worked brilliantly.  Thank you!

[spinfuzer]

on second thought, you might want to put the steps directly in the if then statement because they will both run and reduce execution speed because you are running unncessary steps.  If you put them only in the if statement the second part will only run when the first part fails.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    conditional_stmt = 
        if 
            Time.Second(DateTime.LocalNow()) > 30 
        then     
            let
                next1 = Table.AddColumn(ChangedType, "Column2", each [Column1] + 1),
                final1 = Table.AddIndexColumn(next1, "Index" )
            in
                final1 
        else 
            let
                next2 = Table.AddColumn(ChangedType, "Column2", each [Column1] + 5),
                final2 = Table.NestedJoin(next2, "Column1", next2, "Column1", "NestedJoin" )
            in
                final2
in
    conditional_stmt