Power BI oDATA feed select sharepoint list mulit-value filed error

[Anonymous]

I have issue when I use power BI oData feed to get sharepoint mulit-value field. How to get sharepoint multi-value filed name?

My oData feed url:

"https://xxx/_api/web/lists/GetByTitle('xxxx')/items?$select=Deviation_x0020_Type,Status,Audit_x0020_Coordinator/ImnName$expand=Audit_x0020_Coordinator

The mulit-value field is userinfo list.

The error below:

DataSource.Error: Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed:
OData Version: 3 and 4, Error: The remote server returned an error: (400) Bad Request. (The expression "Deviation_x0020_Type,Status,Audit_x0020_Coordinator/ImnName$expand=Audit_x0020_Coordinator" is not valid.)
OData Version: 4, Error: The remote server returned an error: (400) Bad Request. (The expression "Deviation_x0020_Type,Status,Audit_x0020_Coordinator/ImnName$expand=Audit_x0020_Coordinator" is not valid.)
OData Version: 3, Error: The remote server returned an error: (400) Bad Request. (The expression "Deviation_x0020_Type,Status,Audit_x0020_Coordinator/ImnName$expand=Audit_x0020_Coordinator" is not valid.)

[v-yuta-msft]

@Anonymous ,

The filter grammar in not correct, you can follow the pattern below:

let
   Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItemSnapshot? "
        &"$apply=filter( "
            &"WorkItemType eq 'Bug' "
            &"and StateCategory ne 'Completed' "
            &"and startswith(Area/AreaPath,'{areapath}') "
            &"and DateValue ge {startdate}  "
            &") "
        &"/groupby( "
            &"(DateValue,State,WorkItemType,Priority,Severity,Area/AreaPath,Iteration/IterationPath,AreaSK), "
            &"aggregate($count as Count) "
            &") "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

Reference:

https://docs.microsoft.com/en-us/azure/devops/report/powerbi/create-quick-report-odataq?view=azure-devops

https://crmchartguy.com/2017/01/18/prefilter-data-for-power-bi-with-odata-urls/

Regards,

Jimmy Tao

[Anonymous]

Thanks Jimmy!

I don't have filter in the url, but I want to know how to get sharepoint multivalue field name wiht OData field?

And can you help to correct the url below:

let
Source = OData.Feed("https://{myshare point url}/_api/web/lists/GetByTitle('Deviations')/items?$select=Deviation_x0020_Type,Status,Audit_x0020_Coordinator/ImnName$expand=Audit_x0020_Coordinator", null, [Implementation="2.0"])
in
Source

Thanks!

[Anonymous]

"Audit_x0020_Coordinator/ImnName$expand=Audit_x0020_Coordinator" this is the multi-value field in share point list (it is link to user information page), I checked that Audit_x0020_Coordinator is that list one filed internal name, but I am not sure whether "ImnName" is the multi-value field name. How can I know or get the multi-value field name?