none
Error Handling reformats tables, shifts columns and hides steps RRS feed

  • Question

  • Hi all. I am trying to avoid error pop-ups and provide some information to the user when there is an error building a query. Errors usually happen because there is no data to transform a column or the user did input a wrong value. Errors that can be ignored and continue with other queries

    This is how a successful query loaded to a table and properly formatted looks like

    

    Now, in order to avoid the error message I'm using try/otherwise, which looks like this

    try
    let
         myToken = Json.Document(Web.Contents(baseURL & companyURL & "/login/" & email & "/" & password)),
        Source = Json.Document(Web.Contents(baseURL & companyURL & "/project/"& projectID & "/team", [Headers=[token=myToken[Token]]])),
        members = Source[members],
        #"Converted to Table" = Record.ToTable(members),
        #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"name", "category", "position", "department", "provider", "projectrole"}, {"name.1", "category", "position", "department", "provider", "projectrole"}),
        #"Renamed Columns" = Table.RenameColumns(#"Expanded Value",{{"Name", "email"}, {"name.1", "Name"}, {"category", "Category"}, {"position", "Position"}, {"department", "Department"}, {"provider", "Provider"}, {"projectrole", "Project Role"}})
    in
        #"Renamed Columns"
    otherwise
        let err= #table({"Risk err"},{{"Some error occurred"}}) in #"err"

    With the above solution (and many other variants I tried), I find three problems

    1. If there are errors, the resulting table changes its format. And even if the error is solved afterwards, the format is lost (notice the table name is conserved)

    I have tried to make the style that I want "by default". I've tried to create a new style and make it default for the workbook.

    2. (the most serious) When the error is solved and the query refreshed, the resulting table shifts all columns to the right, because it inserts new columns. The result is a totally messed up layout, not to mention the previous table style problem

    3. Steps are lost. If I use try/otherwise, all steps are lost in the viewer, and I need them because this is supposed to help my users to understand the query.

     Thanks for helping!


    dp

    Sunday, January 13, 2019 7:11 AM

Answers

  • Instead of returning a simple error-message, you can return a table with the same columns as the correct result and put your error message into the fields of its first rows:

    let
         myToken = Json.Document(Web.Contents(baseURL & companyURL & "/login/" & email & "/" & password)),
        Source = Json.Document(Web.Contents(baseURL & companyURL & "/project/"& projectID & "/team", [Headers=[token=myToken[Token]]])),
        members = Source[members],
        #"Converted to Table" = Record.ToTable(members),
        #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"name", "category", "position", "department", "provider", "projectrole"}, {"name.1", "category", "position", "department", "provider", "projectrole"}),
        #"Renamed Columns" = Table.RenameColumns(#"Expanded Value",{{"Name", "email"}, {"name.1", "Name"}, {"category", "Category"}, {"position", "Position"}, {"department", "Department"}, {"provider", "Provider"}, {"projectrole", "Project Role"}}),
    	Result = try #"Renamed Columns" otherwise #table(Table.ColumnNames(#"Renamed Columns"), List.Transform(Table.ColumnNames(#"Renamed Columns"), each {_, "Some error occurred"}))
    in
    	Result

    That saves your layouts.

    ! Code could have errors, as I haven't tried it out. So if you have problems with it that, just post error-message and I'll try to figure out further.

    The query steps disappear everytime you edit what comes after the "in". So I've included the error-handling as a last step in the query and that brings the steps back ;)


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, January 13, 2019 10:31 AM
    Moderator
  • I would recommend that you reference your original query and do this operation on the referenced query instead of everything in just 1 query. The one with all of your steps would be set as a connection only and the new query (with the try otherwise), would be a new one.

    I'm not sure you can keep the format if the columns change. You'd need to keep the exact name of the columns and their data types with some sample data to make this work (like Imke suggested). Your #table would need to define every specific column that needs to come out of that query AND also their datatypes.

    Monday, January 14, 2019 5:19 PM

All replies

  • Instead of returning a simple error-message, you can return a table with the same columns as the correct result and put your error message into the fields of its first rows:

    let
         myToken = Json.Document(Web.Contents(baseURL & companyURL & "/login/" & email & "/" & password)),
        Source = Json.Document(Web.Contents(baseURL & companyURL & "/project/"& projectID & "/team", [Headers=[token=myToken[Token]]])),
        members = Source[members],
        #"Converted to Table" = Record.ToTable(members),
        #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"name", "category", "position", "department", "provider", "projectrole"}, {"name.1", "category", "position", "department", "provider", "projectrole"}),
        #"Renamed Columns" = Table.RenameColumns(#"Expanded Value",{{"Name", "email"}, {"name.1", "Name"}, {"category", "Category"}, {"position", "Position"}, {"department", "Department"}, {"provider", "Provider"}, {"projectrole", "Project Role"}}),
    	Result = try #"Renamed Columns" otherwise #table(Table.ColumnNames(#"Renamed Columns"), List.Transform(Table.ColumnNames(#"Renamed Columns"), each {_, "Some error occurred"}))
    in
    	Result

    That saves your layouts.

    ! Code could have errors, as I haven't tried it out. So if you have problems with it that, just post error-message and I'll try to figure out further.

    The query steps disappear everytime you edit what comes after the "in". So I've included the error-handling as a last step in the query and that brings the steps back ;)


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, January 13, 2019 10:31 AM
    Moderator
  • Thank you so much Imke. 

    Unfortunately I couldn't make it work. 

    The problem is the error can happen at any stage, not just on the last line. The example in the first image of the original post is triggered by the line #2 “myToken=…”

    As a workaround I added “try” to all lines, as a last resource, but it didn’t work. Also, because the last operation #”Renamed Columns” might not have the columns because of a previous error, I created a fixed table

    let
        myToken = try Json.Document(Web.Contents(baseURL & companyURL & "/login/" & email & "/" & password)),
        Source = try Json.Document(Web.Contents(baseURL & companyURL & "/project/"& projectID & "/team", [Headers=[token=myToken[Token]]])),
        members = try Source[members],
        #"Converted to Table" = try Record.ToTable(members),
        #"Expanded Value" = try Table.ExpandRecordColumn(#"Converted to Table", "Value", {"name", "category", "position", "department", "provider", "projectrole"}, {"name.1", "category", "position", "department", "provider", "projectrole"}),
        #"Renamed Columns" = try Table.RenameColumns(#"Expanded Value",{{"Name", "email"}, {"name.1", "Name"}, {"category", "Category"}, {"position", "Position"}, {"department", "Department"}, {"provider", "Provider"}, {"projectrole", "Project Role"}}),
        Result = try #"Renamed Columns" otherwise #table({"Risk err"},{{"Some error occurred"}})
    in
        Result

    I always get “We cannot convert a value of type Record to type Table.” even though the parameters were correct. 

    On the bright side, I have the steps back and the error table doesn’t shift columns. The table style is still changed, though.

    I also used "try" as a block for all lines (with the "otherwise" at the end but before the "in") but it didn't work :\

    Any other ideas? Isn't it there the option to just turn blocking error notifications off? 

    Thanks!


    dp



    • Edited by dpiret Monday, January 14, 2019 12:06 PM
    Monday, January 14, 2019 11:35 AM
  • Hey,

    The easiest way would be to define that optional output at the IN level like this:

    let
    result = [a] + b
    in
     try result otherwise #table({"Error"}, {{"Has Error"}})

    Monday, January 14, 2019 2:26 PM
  • Thanks Miguel. I immediately tried it , but this solution has the same outcome as the original problem: columns inserted, change of table styles and steps lost :\

    Any other ideas? :)


    dp

    Monday, January 14, 2019 4:13 PM
  • I would recommend that you reference your original query and do this operation on the referenced query instead of everything in just 1 query. The one with all of your steps would be set as a connection only and the new query (with the try otherwise), would be a new one.

    I'm not sure you can keep the format if the columns change. You'd need to keep the exact name of the columns and their data types with some sample data to make this work (like Imke suggested). Your #table would need to define every specific column that needs to come out of that query AND also their datatypes.

    Monday, January 14, 2019 5:19 PM
  • Thanks, I got this!

    It's a combination of things, but basically the trick is a) using a referenced query and b) taking the previous Excel resulting table, removing all rows and using that as a result:

    let
        Source = Query1
    in
        try Source otherwise Table.RemoveRows(Excel.CurrentWorkbook(){[Name="tblValues2"]}[Content],0,Table.RowCount(Excel.CurrentWorkbook(){[Name="tblValues2"]}[Content]))

    It doesn't seem datatypes have to be the same, just the columns names. 

    Thanks Miguel and Imke!


    dp

    Thursday, January 17, 2019 12:11 PM