error-handlingpowerbipowerqueryminvalid-argument

Power BI - handling non-existing arguments and returning data in table for valid arguments


I am stuck at the error handling routine. I have this function..

(LicenceNumber) =>
let
Source = 
Web.Page(Web.Contents("http://mbsweblist.fsco.gov.on.ca/ShowLicence.aspx?M" 
& Number.ToText(LicenceNumber) & "~")),
WebData = Source{1}[Data],
#"Extracted Text Before Delimiter" = Table.TransformColumns(WebData, 
{{"Column1", each Text.BeforeDelimiter(_, ":"), type text}}),
#"Removed Top Rows" = Table.Skip(#"Extracted Text Before Delimiter",1),
#"Transposed Table" = Table.Transpose(#"Removed Top Rows"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", 
[PromoteAllScalars=true])
in
#"Promoted Headers"

Which returns data to table

let
Source = {13000246..13000250},
#"Convert to Table" = Table.FromList(Source,Splitter.SplitByNothing(),{"Licence Number"}),
#"Changed Type" = Table.TransformColumnTypes(#"Convert to Table",{{"Licence Number", Int64.Type}}),
#"Get WebData" = Table.AddColumn(#"Changed Type", "WebData", each try WebData([Licence Number]) otherwise #table({},{})),
#"Combine WebData" = Table.Combine(#"Get WebData"[WebData]),
#"Changed Types" = Table.TransformColumnTypes(#"Combine WebData",{{"Agent/Broker Name", type text}, {"Licence #", type text}, {"Brokerage Name", type text}, {"Licence Class", type text}, {"Status", type text}, {"Issue Date", type date}, {"Expiry Date", type date}, {"Inactive Date", type date}})
in
#"Changed Types"

I am trying to error handle a situation where I pass an invalid value in source, lets say source = {13009995..13009999}, this is throwing error - "col X of table was not found". I tried to use the following error handling logic but it is not working ..

Empty = #table({{"Agent/Broker Name", type text}, {"Licence #", type text}, 
{"Brokerage Name", type text}, {"Licence Class", type text}, {"Status", type 
text}, {"Issue Date", type date}, {"Expiry Date", type date}, {"Inactive 
Date", type date}},{}),
Combine = Table.Combine({#"Get WebData"[WebData], Empty}),

I am primarily a business analyst and unable to fix this error. Requesting help. User Olly had helped me with my primary query


Solution

  • I would suggest creating an empty table as a separate query called EmptyTable that matches the columns when you do get data back. Here's the M code for that:

    let
        Empty =
        #table(
            {
            "Agent/Broker Name",
            "Licence #",
            "Brokerage Name",
            "Licence Class",
            "Status",
            "Issue Date",
            "Expiry Date",
            "Inactive Date"
            },
            {}
        )
    
    in
        Empty
    

    Now in your #"Get WebData" step, simply swap out #table({},{}) for EmptyTable.

    #"Get WebData" =
    Table.AddColumn(
        #"Changed Type",
        "WebData",
        each
            try WebData([Licence Number])
            otherwise EmptyTable
    ),
    

    Note: Your query looks to work fine when there is at least one valid license number.