Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
henrikj
Advocate II
Advocate II

Dataflows gen2: Index was outside the bounds of the array

I run into an error that I found no information about and thought I should post about it here and how I managed to at least produce a work-around. The core issue is likely a bug that needs to be resolved.

 

I’ve got this gen2 dataflow that reads data from the Defender API (https://api.securitycenter.windows.com/api/machines). All data are replaced each time we run the dataflow and the data are stored in a Lakehouse. Everything worked fine and the dataflow was refreshed once a day for two weeks without any errors. Two days ago, it started to fail with the error: “Underlying error: An error occurred while writing data: Index was outside the bounds of the array”. No information of which column or value that caused the error. No information through Google/Bing/Documentation, except some mentions of a similar error message in when importing data to Excel from old versions of SQL Server.

 

By gradually removing columns in the dataflow, I was finally able to pinpoint the error to the column exclusionReason. This column currently contains three unique values – null, “DuplicateDevice”, and “Other”. The values “DuplicateDevice” and “Other” occurs only once, while all other rows are null. The data type is set as text in the dataflow. The error goes away if I either remove all null values or one/both text values, i.e., the error is only present when the column contains both text and null values. The filter operation takes place after the data type is set. The error is not related to the final write operation to the lakehouse, as it also occurs without a specified storage location. It might be related to write operations for staging though.

 

When I checked the stored data in the lakehouse from the last successful run, I found that the values “DuplicateDevice” and “Other” were missing, i.e., the text values seem appear for the first time in conjunction with the error.

 

Using the same M code in PBI Desktop and Dataflows gen1 does not produce the error.   

 

I was finally able to rectify the initial error by replacing all nulls in the column with a blank value. However, this resulted in a new error when the data were written to the lakehouse, despite no known changes in data types and/or columns. I had to recreate the destination table, which was only possible since we replace all data at each refresh. When running the dataflow again, the offending table was written without any problem.

 

Strangely, all other (unchanged) queries in the dataflow now failed during the final step, when the result is written to the lakehouse. The error message was slightly different from the initial error (see below). I deleted all destination tables for the dataflow, but this didn’t solve the problem. The initial query was written to the destination, but the other queries were still failing. I once again removed the tables at the destination, but this time I also removed and recreated the destination in the queries. No luck. I now resorted to removing the nulls from all columns, in all queries. This finally solved the issue, but at the expense of the replace operation. A strange consequence of the table wide replace operation was that multiple columns seemed to lose their data type and reverted to Any.

 

I’m afraid that this bug will reappear in the future since we have a lot of columns that currently consist of only null values. And I might not be able to delete the destination table next time without data loss.

 

To summarize: I have a column defined as text. The column previously only contained nulls. When two text values appeared, I got an error and an error message with basic information. The error goes away if a remove all nulls or the text values in the offending column, but I got additional errors when the data for other queries in the dataflow were written to the destination. To solve the secondary errors, I used brute force and removed all nulls in all columns.

 

Complete error message for initial error:

Query: Error Code: Mashup Exception Data Format Error, Error Details: Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: DataSource.Error: Failed to insert a table., InnerException: An error occurred while writing data: #{0}, Underlying error: An error occurred while writing data: Index was outside the bounds of the array. Details: Reason = DataFormat.Error;Message = An error occurred while writing data: Index was outside the bounds of the array.;Message.Format = An error occurred while writing data: #{0};Message.Parameters = {"Index was outside the bounds of the array."} (Request ID: a4df6722-1f67-4761-9c99-5e48e1754997).

 

Complete error message for the final write operation for the other queries:

Mashup Exception Data Format Error Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: DataSource.Error: Error in replacing table's content with new data in a version: #{0}., InnerException: An error occurred while writing data: #{0}, Underlying error: An error occurred while writing data: Index was outside the bounds of the array. Details: Reason = DataFormat.Error;Message = An error occurred while writing data: Index was outside the bounds of the array.;Message.Format = An error occurred while writing data: #{0};Message.Parameters = {"Index was outside the bounds of the array."}

6 REPLIES 6
v-cboorla-msft
Community Support
Community Support

Hi @henrikj 

 

Welcome to Fabric Community and thanks for posting your question here.

 

Both the error message indicates that there is a data format error in your mashup document that is causing Power BI to fail to insert a table into your data source.

 

Here are some troubleshooting tips that will help you to identify data format errors in Power BI and correct it. Here are a few things you can try:

 

  • Check the compatibility of data types between the new table and the original table.
  • Verify that the data in the source column is in the correct format.
  • Convert one of the columns to the same data format as the other column if you are trying to combine two columns with different data formats.
  • Use a different function if the function you are using is not compatible with the data format of your column.
  • Reduce the number of columns in the table if you are trying to insert a table that has too many columns.
  • Verify the connection to your data source and ensure that your credentials are correct.
  • Ensure that the table you are trying to replace has the same number of columns and column names as the original table.
  • Verify that all the values in the new table are within the valid range for the corresponding data type.

 

I hope this helps! Please do let us know in case of any further questions.

This is not a data type error. A text column should be able to contain both nulls and text values. The error appears when I get text data in a text column instead of only nulls, as was the case until the point of failure.

 

Please note that the exact same M code is working for in PBI Desktop and in Dataflows gen1.

 

This is likely a bug in Fabric. Also, it should be possible for you to reproduce it using the M code that I posted. You should be able get access to the Defender API and load data from it. Make sure that you initally load data from a column that only contains nulls. Next, add a few rows with text for the column in question and the load should fail.

 

Finally, please note that there is no information available at all regarding the underlaying error message. In my opinion, this is an argument for a bug; a user/configuration error would likely have resulted in multiple previous post to the forums. 

Hi @henrikj 

 

Apologies for the delay in response.

 

Following up to check whether your issue got resolved. For now there is no bug in Fabric related to this issue, so could you please contact support team for more help.

 

I would request you to please go ahead with Microsoft support for this. Please raise a support ticket on this link: https://support.fabric.microsoft.com/en-US/support/

Also once you have opened the support ticket , please do share the supportcase no. here. 

 

Thanks

 

I've built a workaround by replacing all nulls with "" in the dataflow, as the bug occurs during the writing stage. This is far from perfect, but I will likely not create a support case, as I have a limited amount of time right now. 

Hi @henrikj 

 

Glad to know that your issue got resolved by workaround. Please continue using Fabric Community for help regarding your issues.

Hi @henrikj 

 

Thanks for using Fabric Community and reporting this . 

I have reached for the internal team for help on this. I will update you once I hear from them.

Appreciate your patience.

Helpful resources

Announcements
FabricCarousel_June2024

Fabric Monthly Update - June 2024

Check out the June 2024 Fabric update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.