Error 400: Bad request (Value did not fall within the expected range)

Power Query
1

Having a very hard time figuring out this error in power query.
Inside PowerBI I connect to a SharePoint list (Master AP)
The connection gets established fine.
To access a particular field I need to expand “field values as text” to get to the country field as the field doesn’t automatically appear as a column. The other fields show values without errors. Country is a text field.
However all of the country values return an error. It says “Value did not fall within the expected range” per the Error1 image
The country field inside the Master AP SharePoint list is a lookup to a field from the Country SharePoint list.
Yet when I click in the error cell, it displays the correct country name per the Error2 image

Error1
Error1762×597 50.9 KB

Error2
Error2781×653 30.9 KB

2

@mombo,

I’ve never personally experienced this error, but in doing some checking, came up with a number of potential causes and solutions. Please take a look at this thread and see if any of the proposed approaches work in solving your problem.

  • Brian
3

Hi @Mombo, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

4

Hi @Mombo, we’ve noticed that no response has been received from you since the 20th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

5

Thanks Brian. That’s the exact problem I am having but I checked the data types and they match. The underlying SharePoint lists have a max of 1900 items well under the 5000 list limit. So the solutions didn’t work for me. The other issue I have is that the query takes a very very long time to load in the query editor. So, I am thinking that it is a SharePoint issue. I will export the SharePoint list to Excel and then import the Excel into PowerBI to see if that replicates the problem. If not, it isolates the problem to SharePoint. If it does, it could still be SharePoint or PowerBI.

6

@Mombo,

Have you seen this series from Matt Allington – may be very relevant to your situation:

  • Brian
1 Like
7

I have come up with a workaround solution based on the article https://exceleratorbi.com.au/fast-track-power-bi-dev-with-sharepoint-files/ but I couldn’t use it exactly. His solution solves my latency problem but since my data is in a SharePoint list i couldn’t invoke his file solution. Instead I just exported the SharePoint list to an Excel file hosted on our network drive and linked to it from within PowerBI. It is very quick and no errors. The drawback is that in order to refresh the PowerBI data I have to open each of the two linked Excel files which are set to refresh on open. It only takes a minute. We only need to update once to twice annually so the impact is acceptable. It is still vastly quicker than having to manually update every filed in all 68 reports which is what has been done.

Thanks for the link to the document, it led to my solution.