Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Query Editor - Try query, if error, skip/move on?

So I'm trying to bring in PDF pages into my data model. In order to keep the data ingestion dynamic, I need to setup a try/otherwise statement as some PDF's might have 5 pages, but others only 3. I would have queries setup for each page, but the queries for pages 4 and 5 would fail for a 3 page PDF causing a refresh to fail. 

 

How can I setup m code to try the query, and if it fails, do nothing or just keep going so the entire refresh doesn't fail?

Need Help

5 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    Anonymous can you share M code you are using and I will put the logic to catch the error.

  • v-yingjl's avatar
    v-yingjl
    Community Support
    6 years ago

    Hi Anonymous ,

    You can try to use List.RemoveNulls() function and add try otherwise statement in it, here is the sample you can refer as your need:

    let
    Source =
    Table.Combine(
        List.RemoveNulls({
            Table.FromRecords({[CustomerID = 1, Name = "Bob"]}),  //Query 1
            Table.FromRecords({[CustomerID = 2, Name = "Jim"]}),  //Query 2
            try
            Table.FindText()  //Query 3 which represents error
            otherwise
            null,
            Table.FromRecords({[CustomerID = 3, Name = "AAA"]}),  // Query 4
            try
            Table.FirstValue()  //Query 5 which represents error
            otherwise
            null,
            Table.FromRecords({[CustomerID = 4, Name = "BBB"]})   // Query 6
        })
    )
in
    Source

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      parry2k 

       

      I'm just using a basic import of a page in PDF from SharePoint. No data transformation really at all.

       

      Original Query: 

      let
    Source = SharePoint.Files("https://xxxxx.sharepoint.com/personal/xxxxx_com/", [ApiVersion = 15]),
    #"Filtered Rows2" = Table.SelectRows(Source, each Text.StartsWith([Name], "DailyStatement")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows2",{{"Date created", Order.Descending}}),
    #"Kept First Rows" = Table.FirstN(#"Sorted Rows",1),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Kept First Rows", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (4)", each #"Transform File (4)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (4)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (4)", Table.ColumnNames(#"Transform File (4)"(#"Sample File (4)")))
in
    #"Expanded Table Column1"

       

      Going this route with SharePoint creates the 4 helper queries:

      SampleFile

      let
    Source = SharePoint.Files("https://xxxxxxmy.sharepoint.com/personal/xxxxxxx_com/", [ApiVersion = 15]),
    #"Filtered Rows2" = Table.SelectRows(Source, each Text.StartsWith([Name], "DailyStatement")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows2",{{"Date created", Order.Descending}}),
    #"Kept First Rows" = Table.FirstN(#"Sorted Rows",1),
    Navigation1 = #"Kept First Rows"{0}[Content]
in
    Navigation1

      Parameter

      #"Sample File (4)" meta [IsParameterQuery=true, BinaryIdentifier=#"Sample File (4)", Type="Binary", IsParameterQueryRequired=true]

      Transform File

       

      let
    Source = (Parameter4) => let
        Source = Pdf.Tables(Parameter4, [Implementation="1.1"]),
        Page1 = Source{[Id="Page002"]}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(Page1, [PromoteAllScalars=true])
    in
        #"Promoted Headers"
in
    Source

       

      Transform Sample File

      let
    Source = Pdf.Tables(Parameter4, [Implementation="1.1"]),
    Page1 = Source{[Id="Page002"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Page1, [PromoteAllScalars=true])
in
    #"Promoted Headers"
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        v-yingjl Thanks for the reply. Based on my post above, I think the queries would fail there instead of when I combine the queries (pages) together?