Frustrating Power Query Editor and Sharepoint Lists

[tknguyen]

I've spent days on this when I feel it should be pretty straight foward.

I have various SharePoint lists.. some columns are type lookup, and I'm trying to transform the lists.  I have a very simple list with several columns...  2 of which are Lookup columns to reference 2 other SharePoint lists.  They both show up similarly as follows:

When I expand the SubProductName, i see all columns (including system ones), and am able to select just the one I want (Title), and this works as expected.. however, when I try to do the same for the 2nd Lookup column, it does not see any columns to expand.  The 2nd column is a lookup from a sharepoint list with only 1 column (Title), but it does not see anything and returns "No columns were found", and I can't expand to get the values... Why is this so anti-intuitive???

This is the sharepoint list it's trying to expand:

[v-shamiliv]

Hi @tknguyen 

We are following up once again regarding your query. Could you please confirm if the issue has been resolved through the support ticket with Microsoft?

If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. If we don’t hear back, we’ll go ahead and close this thread.

Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you for your understanding and participation.

[v-shamiliv · Accepted solution]

Hi @tknguyen 
If the issue still persists we recommend you to raise support ticket.

https://learn.microsoft.com/en-us/power-bi/support/create-support-ticket

If this solution helps, please consider giving us Kudos and accepting it as the solution so it can assist other community members.
Thank you.

[v-shamiliv]

Hi  @tknguyen 
The reason you're seeing all nulls is because, in your case, ReleaseType isn't a record; it's already a plain text value (like "Major"). This occurs because SharePoint automatically simplifies lookup fields when the source list only has one column (such as just Title). Do not attempt to expand or extract [Title]. Instead, use the ReleaseType column as it is ,it's already a simple text value containing your lookup (such as "Major", "Fix", etc.). There is no need for any custom column formulas for this field. Simply rename the ReleaseType column if you prefer a cleaner name and proceed with your transformations as usual.
I have included the official Microsoft documentation for your reference:
Power Query SharePoint list connector - Power Query | Microsoft Learn

If this solution helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community
Thank you.

[tknguyen]

Hi

As you can see in my screenshot from Power BI it shows as a record, NOT plain text...

I can not expand the column as it reports no columns were found

If you are looking at the screenshot that shows it as actual value, then this is NOT in power BI, but directly in SharePoint as the list:

[v-shamiliv]

Hi @tknguyen 

It appears that the issue arises from Power BI inconsistently interpreting the ReleaseType column as either a record or a text value. This typically occurs when SharePoint lookup columns are involved, leading to inconsistencies due to the structure of the list or Power Query’s automatic type detection.

To address this, standardize the column to text by applying a transformation. Add this step in the Advanced Editor after the Source step:

= Table.TransformColumns(Source, {{"ReleaseType", each if Value.Is(_, record) then Record.Field(_, "Title") else Text.From(_), type text}})

This approach avoids nulls, handles records and text efficiently, and simplifies your Power Query.

If this solution helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community
Thank you.

[tknguyen]

I'm getting an expression error.. can you please confirm what is wrong (NOTE there were no syntax errors detected):

[tknguyen]

Anyone else have any suggestions?

[v-shamiliv]

Hi @tknguyen 

Thank you for reaching out microsoft fabric community forum. 

That error usually means ReleaseType isn't always a record—Power BI might just be seeing a plain value or null, especially if the source list only has one column like Title.

Try using this formula in a custom column:

if [ReleaseType] is record then [ReleaseType][Title] else null

This checks if it’s a record before trying to get the Title, which avoids errors.

Also, in Power Query, click the icon next to the 

ReleaseType column name to check its actual data type. If it doesn’t show as a record, that confirms the issue.

If this solution helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community
Thank you

[tknguyen]

With the formula, I'm getting null for both entries, which makes no sense at all to me:

Per my previous screenshot of the Sharepoint list as seen in SharePoint, these are the only 2 rows in that list, and both have valid lookup values which I'm posting again:

[rajendraongole1]

Hi @tknguyen  - can you add a custom column with below formula 

= Record.Field([ReleaseType], "Title")

after that, Name the new column appropriately. This works because you’re directly telling Power BI: “I know there’s a field called Title in that record—just get it.”

Please check and let know.

[tknguyen]

Thank you for the quick response, but I get this error:

Here is the sharepoint list it's looking up: