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?  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

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

6 Replies

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