Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Conditionally Skip an Applied Step

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...
Need Help
  • spinfuzer's avatar
    spinfuzer
    2 years ago
    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
spinfuzer's avatar
spinfuzer
Solution Sage
2 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      spinfuzer
      Solution Sage
      2 years ago
      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's avatar
        Anonymous
        Not applicable
        2 years ago

        This worked brilliantly.  Thank you!

  • Brett007's avatar
    Brett007
    Helper III
    2 years ago

    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)),