Append Query Errors not displaying

[Darren_Linc]

Hello,

Bit of an odd one, after an append query and when refreshing the append query, out of 1000 records 700 of which are errors. When I select view errors the data-table is blank.

Context Info
I'm connecting to a Excel document on a document folder on SharePoint Online, the document has five worksheets each of which I pull into PBI, each worksheet then has a Transform File from x associated. I then use append query (as new table) to combine the five worksheets into one dataset. This processes with no errors. However when I refresh the set of queries the new append table is flagged as containing 700+ errors, if I select View Errors the query is refreshing (the five yellow moving dots top of the query UI) and then no data is displayed.
I've checked the original source document, it's not checked out to anyone neither has it been amended or anything locked. I''ve gone through the data-tables to see if I can spot the errors manually but everything looks ok.

I'm going to go through the data-types to see if there's some kind of conflict on the base queries but if anyone has any ideas?

[v-huizhn-msft]

Hi @Darren_Linc,

I can not reproduce your screnario, could you please share your screenshot for further analysis?

You have confirmed all the five worksheets haven't beed changed at all? In addition, when we refresh the data, Power Query will be executed to transform data. Please click the Advanced Editor, and check if there is other operation statement.

Best Regards,
Angelia

[Darren_Linc]

Good Morning, screenshots attached FYI. I've narrowed it down to the one worksheet which is causing the errors by reducing the number of tables in the append query. Still not displaying the errors though. Here's the advanced editor code from errors in the append query.

let
Source = Append1,
#"Added Index" = Table.AddIndexColumn(Source, "Row Number" ,1),
#"Kept Errors" = Table.SelectRowsWithErrors(#"Added Index", {"Source.Name", "UPRN", "Address 1", "Address 2", "Local Authority", "Postcode", "Component", "Service Area", "Date of Fitting", "Month Completed", "Lifespan Budget", "Total Value of Orders raised (Ex VAT)", "Total Value of Orders Raised (Inc VAT)", "Variance from Lifespan Budget", "Lifespan updated", "Planned Received", "Quote Received", "Reason For Omission", "Letter sent to tenant", "Comment", "Orders Raised", "Contractor", "Redec Raised", "Month of fitting", "Programmed Month", "Comments", "New Install Cert", "RAM"}),
#"Reordered Columns" = Table.ReorderColumns(#"Kept Errors", {"Row Number", "Source.Name", "UPRN", "Address 1", "Address 2", "Local Authority", "Postcode", "Component", "Service Area", "Date of Fitting", "Month Completed", "Lifespan Budget", "Total Value of Orders raised (Ex VAT)", "Total Value of Orders Raised (Inc VAT)", "Variance from Lifespan Budget", "Lifespan updated", "Planned Received", "Quote Received", "Reason For Omission", "Letter sent to tenant", "Comment", "Orders Raised", "Contractor", "Redec Raised", "Month of fitting", "Programmed Month", "Comments", "New Install Cert", "RAM"})
in
#"Reordered Columns"

Here's the code from the table containing the errors, please note BI only inform me that there are errors when refreshing the completed Append query. Not during the initial evaluation/processing section.

let
Source = SharePoint.Files("https://linccymruadmin.sharepoint.com/team-sites/asset-management-team/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "Planned Programme 2017.18.xlsx")),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Rows", "Transform File from Redecoration", each #"Transform File from Redecoration"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Redecoration"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Redecoration", Table.ColumnNames(#"Transform File from Redecoration"(#"Sample File (5)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"UPRN", type text}, {"Address 1", type text}, {"Address 2", type text}, {"Local Authority", type text}, {"Postcode", type text}, {"Component", type text}, {"Service Area", type text}, {"Programmed Month", type any}, {"Month Completed", type text}, {"Lifespan Budget", Currency.Type}, {"Total Value of Orders raised (Ex VAT)", Currency.Type}, {"Total Value of Orders Raised (Inc VAT)", Currency.Type}, {"Variance from Lifespan Budget", Currency.Type}, {"Lifespan updated", type any}})
in
#"Changed Type"

[v-huizhn-msft]

Hi @Darren_Linc,

 You want to combine the five worksheets into one dataset. From the screenshot, there are four queries work fine. The left one has an error message, right? Please upload the left one worksheet lonely, and check if it works fine.

Best Regards,
Angelia

[MarcelBeug]

In general, this phenomenon - errors, but an empty error report - happens when actual values conflict with column types.

Within the query editor, this is allowed and not considered an error.

When the data is loaded, those values may generate errros.

The error report however, gives no result as this is composed in Power Query where everything is considered fine.

One way to check in Power Query is add some code (or a separate query), that will raise errors also in Power Query, e.g.:

let
    Source = #table(type table[Currency = Currency.Type],{{"1.234,56"},{1234.56}}),
    MyTable = Table.AddIndexColumn(Source, "Index", 0, 1),
    CheckCurrency = Table.TransformColumns(MyTable, {{"Currency", each try if Currency.From(_) = _ then _ else error "Invalid Value" otherwise error "Invalid value"}})
in
    CheckCurrency

This code checks if all values in the "Currency" column are of type Currency; if not, then an error is raised.

Note: each value type in Power Query has its own conversion function (Int64.From, Text.From, etcetera).