Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Rocky_Brown
Helper I
Helper I

M-Code

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

3 REPLIES 3
amitchandak
Super User
Super User

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

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

 

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

 

Thanks,

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.