M-Code

[Rocky_Brown]

I have the following m-code to access DevOps server.  It is working, however, i get no data back in my table.  I am assuming is it due to the names of my columns in Dev Ops have spaces in the names.

Here is a listing of the column names:

Area Path, Assigned To, Iteration Path, Work Item ID, Work Item Type.

How do I code the below when I have spaces in the column names?

let
Source = OData.Feed("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItems?"
&"$filter=WorkItemType eq 'Feature'"
&" and State ne 'Cut'"
&" and startswith(Area/AreaPath,{Area Path})"
&" and Descendants/any()"
&"& $select=WorkItemId,Title,WorkItemType,State,AreaSK"
&"& $expand=AssignedTo($select=UserName),Iteration($select=IterationPath),Area($select=AreaPath),"
&"Descendants("
&"$apply=filter(WorkItemType eq 'User Story')"
&"/aggregate($count as CountOfUserStories, StoryPoints with sum as TotalStoryPoints)"
&")",
null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]),
#"Expanded Descendants" = Table.ExpandTableColumn(Source, "Descendants", {"CountOfUserStories", "TotalStoryPoints"}, {"Descendants.CountOfUserStories", "Descendants.TotalStoryPoints"}),
#"Expanded Area" = Table.ExpandRecordColumn(#"Expanded Descendants", "Area", {"AreaPath"}, {"Area.AreaPath"}),
#"Expanded Iteration" = Table.ExpandRecordColumn(#"Expanded Area", "Iteration", {"IterationPath"}, {"Iteration.IterationPath"}),
#"Expanded AssignedTo" = Table.ExpandRecordColumn(#"Expanded Iteration", "AssignedTo", {"UserName"}, {"AssignedTo.UserName"})
in
#"Expanded AssignedTo"

Any help would be appreciated.

Thank you

[amitchandak]

@Rocky_Brown , column names can have space, But when they are used in steps, there also space should come

[Rocky_Brown]

I tried adjusting the code to something like this:

&"$filter=Work_x0020_Item_x0020_Type eq 'Feature'"

Using the _x0200_ as the space for the column name and get an error 

DataSource.Error: OData: Request failed: The remote server returned an error: (400) Bad Request. (VS403483: The query specified in the URI is not valid: VS403522: The property 'Work_x0020_Item_x0020_Type' is not available on the specified Project(s). Please remove 'Work_x0020_Item_x0020_Type' from your query and try again..)
Details:
DataSourceKind=OData
DataSourcePath=http://devops.janus.com/JanusIntl/All._ActiveWork/_odata/v3.0-preview/WorkItems
Url=http://devops.janus.com/JanusIntl/All._ActiveWork/_odata/v3.0-preview/WorkItems?$filter=Work_x0020_I... eq 'Feature' and State ne 'Cut' and startswith(Area/AreaPath,'ALL._ActiveWork') and Descendants/any()& $select=WorkItemId,Title,WorkItemType,State,AreaSK& $expand=AssignedTo($select=UserName),Iteration($select=IterationPath),Area($select=AreaPath),Descendants($apply=filter(WorkItemType eq 'User Story')/aggregate($count as CountOfUserStories, StoryPoints with sum as TotalStoryPoints))

Can you help me with how to adjust the code in order for me to return data?

Thank you

[Rocky_Brown]

I tried coding with spaces and kept getting errors.  Looking for help to modify the code with spaces in my column names.

Thanks,