Newbie - Refresh Error:---- Expression. Error: The index is outside the bounds of the record.

[jimmyg706]

Hi All

I am getting table refresh errors and I can replicate the issue.

My Data source has 1892 records. (Its an ASANA table if you reall want to know)

When I add a new record, I get an error.

So using Desktop   [Refresh Preview] it shows as follows:

Fig 1

Expression.Error: The index is outside the bounds of the record.
Details:
Record=[Record]
Index=36

Using Power BI service the error shows as the following and note I had to hide some PII information

Fig 2

If I remove the record (back to 1892)  I added all is good.

The error from PBI points to anotherr record and I can find that reciord and it looks 100% ok

So I thought I will really dumb down my table. I removed all relationships and all other tables so Im looking at a simple table (albeit there is transformation)

It’s the same. I’m just wondering what’s going on and how to fix it?

Advice Appreciated

Jimmy

[jimmyg706]

@pmreis 

All sorted. There were some strange things with the order of the transformation steps.

So your advice worked a treat. 

[jimmyg706]

@pmreis 

I would have attached a video but I cant -so Indeed I stppedd through my transformation steps below and where you see a green indicator all is good

Then  when I click Pivot the Clumn this si hwre the error is appearing.  In fact the error is there all the time. When I thought i could replciate it; that was a wild goose chase

Im just wondering what to do next?  should I pivot again and remove the version that doesnt work?  Im doing this form  a copy of my PBIX Original 

[jimmyg706]

thanks @pmreis    How do you limt my data set to a small subset?

Where  you mentiion:

 Limit Rows for Testing:

• Temporarily filter the table in Power Query to only include a subset of rows around the problematic index. This can help narrow down the issue and make testing faster.

[pmreis · Accepted solution]

Hi @jimmyg706 

Here are a couple of steps you can try to identify the root cause. Likely going through points 1, 2 and 3 will locate your issue.

1. Check the Transformation Steps:

• Open the Power Query Editor in Power BI Desktop.
• Go through each step of the transformation for the ASANA table. Pay special attention to any steps that involve filtering, removing, or reordering rows or columns.
• Look for any step that might be causing the index to go out of bounds, especially if it's referencing a specific index or field.

2. Inspect the Problematic Record:

• Since the error points to a specific index, try to locate and inspect that record in both the source data and after each transformation step in Power Query. This can help identify if there's something unique or different about that record.

3. Limit Rows for Testing:

• Temporarily filter the table in Power Query to only include a subset of rows around the problematic index. This can help narrow down the issue and make testing faster.

4. Check Custom M Code:

• If you have any custom M code or functions in your transformations, ensure they can handle all scenarios, especially edge cases related to the number of records.

5. Rebuild the Table:

• As a last resort, consider duplicating the table and gradually applying each transformation step to the duplicate. This can help identify the exact step causing the issue.

6. External Factors:

• Ensure that there are no external factors affecting the data source, like API limits (if connecting to ASANA via an API) or changes in the data structure.

7. Update Power BI Desktop:

• Ensure you're using the latest version of Power BI Desktop. Sometimes, bugs or issues are resolved in newer versions.
  
  
  
  Pedro Reis - Data Platform MVP / MCT
  Making Power BI and Fabric Simple
  
  If my response resolved your issue, please mark it as a solution to help others find it. If you found it helpful, please consider giving it a kudos. Your feedback is highly appreciated!
  
  Find me at LinkedIn