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

[Anonymous]

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?

[v-yingjl]

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]

@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]

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

[Anonymous]

Any ideas on how to implement this?

[parry2k]

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