Power Query SharePoint List Error with Allow Multiple Selection on People or Group Field

[stvcdms]

Hello,

I noticed today that if I am trying to connect to a SharePoint list and that list has a People or Group field with the allow multiple selection option enabled, that it causes a null value error. Changing the field to disable allow multiple selection resolves the issue. Has anyone else noticed this or know a way around this?

Steve

[loicr]

I was looking for the same thing. The best workaround I have found is to expand the field "FieldValuesAsText". It won't help everyone but in my case, it was enough.

[Anonymous]

Any solution for this issue? 

[Sam88]

Hi!
is there any news on this topic?

I read in other post the possible temporary workaround, filling null values or disableallowing multiple selection.

thanks.

best regards.

[DanielaHo]

Hello - I am still facing this issue.. any fix yet? 

Changing API to 14 resolves the prolem BUT as a result the multi-select columns only return the first value, not all of them. So that's not a very useful 'fix'... 😞

[karo]

Hi @DanielaHo Unfortunately nothing  new what I am aware of - except of changing Sharepoint List configuration 😞

[DanielaHo]

Well the strange thing is that I have a different list with exactly the same column - multi-select choice - and Power BI is handling it perfectly fine!? So it does not seem to be an issue across the board.

[karo]

Hi @DanielaHo , Just only out of my curiosity, does your list contains Nulls?

In my case conenction Multiple Selection on People or Group Field with Nulls are the issue 😞

Regards,

Karo

[DanielaHo]

I am not using people picker columns, no. I did go and populate all cells to ensure there are NO BLANKS but still it is not working... 

[karo]

Any updates regarding this error? It seems that the issue occures since 2015 and still not resolved...

[bmcilquham]

Is there any update to this?

If I was to use the OData fix, I don't get columns "Managed Metadata" column types returned.  If I change the "[ApiVersion = 15]" from 15 to 14 it switches me back to SP2010 which doesn't allow me access to "Managed Metadata" column types either.  Setting certain columns to not be null or not multi-select is not an option.

Environment:

• SP2013
• Power BI Desktop version 2.56.5023.942 64-bit (March 2018)

[jup]

same problem for me with multi selection columns and even the version column !

[Veles]

I'm still getting this problem. I have a few Person columns that are single selection only and I am able to exapnd those fine. I have one multi select column which shows as a table rather than a record and I get an error when I try and expand it.

I just want to get a colon separated list of names like you do when you export to Excel.

Very frustrating!

[motoray]

Am still running into this issue as of today. Hard to believe it's still not working correctly--the thread is a year old.

[Anonymous]

Facing the same " We couldn't parse OData response result" error. Is there any way to deal with this? Need to fix it urgently! 

[DavidSmith]

I just downloaded this product to confirm that the editor (and problem) is the same as the Power Query engine in Excel 2013, and it is. Those of you changing your ApiVersion to 14 still get an error because the 15 version uses different filtering syntax than the 14 version - the error is with the second line.

If you delete the second step and have the query pull from source, you can then visually navigate through the UI to your list and it should work. Or, you can open the advanced editor from a blank query and just paste / replace the default text with this:

let
    Source = SharePoint.Tables("https://YourSharePointServerHere.com", [ApiVersion = 14]),
    List = Source{[Name="YourListNameHere"]}[Content]
in
    List

The alternative is to learn a little OData syntax and query through ListData.svc, which is a little trickier (but seems to be much more efficient).  Or, you know, maybe have the SharePoint team actually fix the 15 API.  But you shouldn't be holding your breath (or worse, giving up on it!) in the meantime.

[ThoSch]

same here:

i try this:

let
    Source = SharePoint.Tables("https://YourSharePointServerHere.com", [ApiVersion = 14]),
    List = Source{[Name="YourListNameHere"]}[Content]
in
    List

an get this error:

Expression.Error: The key didn't match any rows in the table.
Details:
    Key=Record
    Table=Table

[Gopher500]

Changing to [ApiVersion = 14] in Source fixed my problem with the SharePoint List column names not updating to the current column SharePoint title when pulling the list into power Bi. Microsoft should fix this for 15!

The problem "Expression.Error:" went away for me if you rebuild the applied steps  in the query editor after the Navigation (default name) step. (Note:  in ThoSch's code step was called List.)

[rnoone]

Does anybody have an idea when this SharePoint issue will be resolved?

[EvanS]

Would love to find a resolution or workaround, stuck on this error myself. Tried the API to 14, once that change is done I can no longer see the task list in availble lists.

[EvanS]

Would love to see a resolution as well, cannot seem to work around the error myself.