cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Darren_Linc Helper I
Helper I

Append Query Errors not displaying

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?

4 REPLIES 4
Microsoft v-huizhn-msft
Microsoft

Re: Append Query Errors not displaying

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 Helper I
Helper I

Re: Append Query Errors not displaying


Good Morning, screenshots attached FYI. BI Error.PNGBI Error2.PNGI'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"

Microsoft v-huizhn-msft
Microsoft

Re: Append Query Errors not displaying

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 Community Champion
Community Champion

Re: Append Query Errors not displaying

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).

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors