Error when expanding SharePoint lookup field in Power Query Editor

[biterbit]

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. 

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.

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?

Thanks for any help you can offer.

[Syndicate_Admin]

Hello 👋

Please tell me how you managed to expand the Sharepoint search columns in Power Bi @Syndicate_Admin

[biterbit]

To expand a column, you click on the button at the right-hand side of the column header and select Extract values. The field contents will change from List to Record. Then click on the same button again, and you will see the list of available fields. Those which are selected will be turned into new columns.

[PhilipTreacy · Accepted solution]

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.

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