cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
THEG72 Member
Member

Error Handling for Data Sources

Hi Community,

 

I have a project where i am building a template data model to use accross multiple data sources.

 

In some cases the queries i written have no records and the data source returns a null.

The first query below shows the items, nextpagelink and count.

 

Nullsource.PNGSource Data shows null recordsNullsource2.PNGQuery tries to expand expand null data

My query above fails as no data and cause an error on refreshing...

 

What kind of error handling can i add to this code ?

 

Here is the code for the data table that wont run as null records.

let
    Source = ItemSource,
    Items = Source[Items],
    #"Converted to Table" = Table.FromList(Items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded {0}" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"UID", "Number", "Name", "IsActive", "Description", "UseDescription", "CustomList1", "CustomList2", "CustomList3", "CustomField1", "CustomField2", "CustomField3", "QuantityOnHand", "QuantityCommitted", "QuantityOnOrder", "QuantityAvailable", "AverageCost", "CurrentValue", "BaseSellingPrice", "IsBought", "IsSold", "IsInventoried", "ExpenseAccount", "CostOfSalesAccount", "IncomeAccount", "AssetAccount", "BuyingDetails", "SellingDetails", "PhotoURI", "URI", "RowVersion"}, {"UID", "Number", "Name", "IsActive", "Description", "UseDescription", "CustomList1", "CustomList2", "CustomList3", "CustomField1", "CustomField2", "CustomField3", "QuantityOnHand", "QuantityCommitted", "QuantityOnOrder", "QuantityAvailable", "AverageCost", "CurrentValue", "BaseSellingPrice", "IsBought", "IsSold", "IsInventoried", "ExpenseAccount", "CostOfSalesAccount", "IncomeAccount", "AssetAccount", "BuyingDetails", "SellingDetails", "PhotoURI", "URI", "RowVersion"})
in
    #"Expanded {0}"

can i use a try or otherwise?

 

Or can i stop the queries when detecting null records at the source?

 

The source shows "COUNT" "NULL"...can i make this a query and use some kind of if statement..so if the data source Query shows a null count the other queries wont run?

 

Cheers for any suggestions for error handling.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Error Handling for Data Sources

@THEG72,

 

You may use the try expression.

https://msdn.microsoft.com/en-us/query-bi/m/power-query-m-language-specification

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Error Handling for Data Sources

@THEG72,

 

You may use the try expression.

https://msdn.microsoft.com/en-us/query-bi/m/power-query-m-language-specification

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
THEG72 Member
Member

Re: Error Handling for Data Sources

 

@v-chuncz-msft

So here is my Item Data Source query which is the endpoint containing stock item list.

 

let
    Source =
        Json.Document(
            Web.Contents(SelectedCompany&"/Inventory/Item/"&APIVersion))
in
    Source

I have two data sources accessed using a SelectedCompany parameter.

Object.PNGCompanyA has Item Ledger DataSource shows 38 items

  Object2.PNGCompanyB has NO items

I then have a second reference query that expands out the table data. This works fine for Company A but fails on Company B as follows;

Object3.PNGFails when get to Expand step as records are null

How would i write the try statement into the code below? I had a few goes but get an error

 

let
    Source = ItemSource,
    Items = Source[Items],
    ToTable = Table.FromList(Items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expand = Table.ExpandRecordColumn(ToTable, "Column1", {"UID", "Number", "Name", "IsActive", "Description", "UseDescription", "CustomList1", "CustomList2", "CustomList3", "CustomField1", "CustomField2", "CustomField3", "QuantityOnHand", "QuantityCommitted", "QuantityOnOrder", "QuantityAvailable", "AverageCost", "CurrentValue", "BaseSellingPrice", "IsBought", "IsSold", "IsInventoried", "ExpenseAccount", "CostOfSalesAccount", "IncomeAccount", "AssetAccount", "BuyingDetails", "SellingDetails", "PhotoURI", "URI", "RowVersion"}, {"UID", "Number", "Name", "IsActive", "Description", "UseDescription", "CustomList1", "CustomList2", "CustomList3", "CustomField1", "CustomField2", "CustomField3", "QuantityOnHand", "QuantityCommitted", "QuantityOnOrder", "QuantityAvailable", "AverageCost", "CurrentValue", "BaseSellingPrice", "IsBought", "IsSold", "IsInventoried", "ExpenseAccount", "CostOfSalesAccount", "IncomeAccount", "AssetAccount", "BuyingDetails", "SellingDetails", "PhotoURI", "URI", "RowVersion"})
in
  Expand

I have tried this for the source which works

 

let
    Source =
        try
        Json.Document(
            Web.Contents("http://localhost:8080/AccountRight/?api-version=v2")
                    )
        otherwise
        "Error!"    
in
    Source

However how do i do the Item ledger query error handling when the count is shown as null at the source? I have tried code as follows but get "Token Identifier expected" and couldn’t work out where the comma or bracket error...

 

How can i put error handling into statement below so that IF COUNT = NULL at the source query ItemSource....the referenced query that expands the records (ItemLedger) output message "no records found" and not complete rest of steps?

 

let
    Source =
        try
        ItemSource,
        Items = Source[Items],
        ToTable = Table.FromList(Items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        Expand = Table.ExpandRecordColumn(ToTable, "Column1", {"UID", "Number", "Name", "IsActive", "Description", "UseDescription", "CustomList1", "CustomList2", "CustomList3", "CustomField1", "CustomField2", "CustomField3", "QuantityOnHand", "QuantityCommitted", "QuantityOnOrder", "QuantityAvailable", "AverageCost", "CurrentValue", "BaseSellingPrice", "IsBought", "IsSold", "IsInventoried", "ExpenseAccount", "CostOfSalesAccount", "IncomeAccount", "AssetAccount", "BuyingDetails", "SellingDetails", "PhotoURI", "URI", "RowVersion"}, {"UID", "Number", "Name", "IsActive", "Description", "UseDescription", "CustomList1", "CustomList2", "CustomList3", "CustomField1", "CustomField2", "CustomField3", "QuantityOnHand", "QuantityCommitted", "QuantityOnOrder", "QuantityAvailable", "AverageCost", "CurrentValue", "BaseSellingPrice", "IsBought", "IsSold", "IsInventoried", "ExpenseAccount", "CostOfSalesAccount", "IncomeAccount", "AssetAccount", "BuyingDetails", "SellingDetails", "PhotoURI", "URI", "RowVersion"})
        otherwise
        "NoDataPresent"
in
    Source

 

mpontrelli Frequent Visitor
Frequent Visitor

Re: Error Handling for Data Sources

Hi,

 

Did you ever get a solution for this question?

 

Thanks

 

Mike

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors