OLE DB or ODBC error: [Expression.Error] The index is outside the bounds of the record.

[jimmyg706]

Morning All

Ive seen so many threads on this but none of them helped me.

I was also not sure  on whether I should post in Desktop or here.

Today I had this error on my dataset and all refreshes failed

OLE DB or ODBC error: [Expression.Error] The index is outside the bounds of the record.

I decided to hone in I removed all othe tables and relationships and ended up with this simple table.

It connects to an Asana Datasource (via API)

Nothing was changed that I’m aware of , in the source.

So Ive shown some transformation steps below and Im looking for some advice on next steps.

Step 4 where I pivoted is where it seems to break?   and im stuck

Comments and advice welcome.

Jimmy

Step 1

Was really just to show you the error in PBI Desktop.

Step 2

Step 3

I went down each transformation step until I hit the error/ Note this is the last postive step with no error.

 

Step 4

This the step where I hit the error

Error shows here on pivot

[jimmyg706]

@v-junyant-msft 

Great advice. Its very frustrating. Luckily I can easily remove the records from the data source (I sort of move them away to a holding area) and I can move items back one by one or in bulk.

So similar to what you said; reduce the scale and dig deeper.
Good news I found the rouge entry though its been there for months. So it remains isolated (it was actually a template record/task) and its not a VIP record.
However great advice and thanks for the support and the reply to the other post.

[v-junyant-msft · Accepted solution]

Hi @jimmyg706 ,

You can try to follow these steps:
1. Before applying the pivot transformation, closely examine the data to ensure there are no unexpected nulls, blanks, or inconsistencies in the columns you're using for pivoting. Sometimes, missing or inconsistent data can lead to issues during the pivot operation.
2. Ensure that your Power BI Desktop application and any connectors or extensions you're using (such as the one for Asana) are up to date. An outdated application or connector can sometimes cause unexpected errors.
3. If possible, try simplifying the pivot operation by reducing the number of columns involved or by applying the pivot to a smaller subset of your data. This can help identify if the complexity of the data or the operation itself is causing the issue.

If the above suggestions don't help, can you provide the full code in the Advanced Editor?

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jimmyg706]

Step 1 - Was really just to show you the error in PBI Desktop.