cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
biterbit
Frequent Visitor

Error when expanding SharePoint lookup field in Power Query Editor

I am setting up a new Power BI project based on a number of interlinked SharePoint tables. I've found out how to expand Lookup fields to enable replationships between tables, but in one case this is not working. I have a column (Centre) in one table that is a lookup to the Centres table. It is initially displayed with the usual List placeholder values. 

biterbit_0-1636830529139.png

I then click on the Expand button in the header and select Expand to New Rows and it changes the fields to say Record, but the top line of the table turns from green to red stripes, indicating an error.

biterbit_1-1636830696432.png

I don't see an error message anywhere on the screen - is there somewhere I can look to see why it doesn't like expanding this particular Lookup column?

 

I tried ignoring the error indicator and selecting a field value but the data refresh then failed. All the other lookup columns I have tried work fine - is there something I should be looking for to explain the way this column is behaving?

biterbit_2-1636831023383.png

 

Thanks for any help you can offer.

 

 

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

Hi @biterbit 

 

On the column of Records with errors, select the column then from the Home tab of the Ribbon, click on Keep Rows then Keep Errors.

 

This should give you a column containing any errors in it.  You can then examine the errors by clicking into the column beside the word Error to see what is wrong.

error2.png

 

If you have lots of errors there are other things you can do create columns showing the error message and details.  This involves creating columsn using try otherwise to extract the Error Record information.  Post back here if you need me to write this for you.

 

FYI Dealing with errors | Microsoft Docs

 

Regards

 

PHil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

1 REPLY 1
PhilipTreacy
Super User
Super User

Hi @biterbit 

 

On the column of Records with errors, select the column then from the Home tab of the Ribbon, click on Keep Rows then Keep Errors.

 

This should give you a column containing any errors in it.  You can then examine the errors by clicking into the column beside the word Error to see what is wrong.

error2.png

 

If you have lots of errors there are other things you can do create columns showing the error message and details.  This involves creating columsn using try otherwise to extract the Error Record information.  Post back here if you need me to write this for you.

 

FYI Dealing with errors | Microsoft Docs

 

Regards

 

PHil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!