Dynamic link issue

[ferryv]

Hi.

I wonder if anyone can help. I am working on a project, where I want to use oData feeds to retrieve data from Azure DevOps. As projects are added/removed in Azure DevOps regularly, I want to use a sharepoint list for an administrator to add the ADO organisation (there are multiple) and Area Path of the project and use the imported list in PowerBI to enerate the oData feed info to avoid havng to manually update this in the report each time. In the example, the sharepoint list is stored in table "ADO Feed Info"

I tried 2 options:

let
#"Retrieve oData" = Table.AddColumn(#"ADO Feed Info", "FeedData", 
each OData.Feed ("https://analytics.dev.azure.com/" & [Azure DevOps Organisation] & "/" & Text.BeforeDelimiter([Azure DevOps Area Path],"\") & "/_odata/v3.0-preview/WorkItems?"
&"$filter=contains(Area/AreaPath,'"&[Azure DevOps Area Path]&"')"
&"and WorkItemType eq 'Product Backlog Item'" 
&"&$select=WorkItemId,Title,State,Effort,OriginalEstimate,ParentWorkItemId,CreatedDate,ActivatedDate,StateChangeDate,ClosedDate,WorkItemType,TagNames"
&"&$expand="
&"Iteration($select=IterationPath),"
&"Area($select=AreaPath)"
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])), 
#"Get Column Names"= Table.ColumnNames ( Table.Combine ( #"Retrieve oData"[FeedData] ) ),
#"Expand oData" = Table.ExpandTableColumn(#"Retrieve oData","FeedData",#"Get Column Names")
in 
#"Expand oData"

This pulls in the data ok in PowerBI desktop, but when publishing the report cannor be refreshed due to an "This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources." issue. 

I also tried:

let

#"Retrieve oData" = Table.AddColumn(#"ADO Feed Info","FeedData", each OData.Feed (
Web.Contents("https://analytics.dev.azure.com",
[
RelativePath="""" & [Azure DevOps Organisation] & "/" & Text.BeforeDelimiter([Azure DevOps Area Path],"\") & "/_odata/v3.0-preview/WorkItems",
Query=[
filter="contains(Area/AreaPath,'"&[Azure DevOps Area Path]&"') and WorkItemType eq 'Product Backlog Item'",
select="WorkItemId,Title",
expand="Iteration($select=IterationPath),Area($select=AreaPath)"
]
]
),null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]
)
), 
#"Get Column Names"= Table.ColumnNames ( Table.Combine ( #"Retrieve oData"[FeedData] ) ),
#"Expand oData" = Table.ExpandTableColumn(#"Retrieve oData","FeedData",#"Get Column Names")
in 
#"Expand oData"

When replacing "Web.Contents"with "VSTS.AccountContents" this requires a login and this doesn't accept the credentials (Organisational Account) and shows an error: "Null Value for Resource Path"

PS:

I also tried 

let

#"Retrieve oData" = Table.AddColumn(#"ADO Feed Info","FeedData", each OData.Feed (
Web.Contents("https://analytics.dev.azure.com",
[
RelativePath=[Azure DevOps Organisation] & "/" & Text.BeforeDelimiter([Azure DevOps Area Path],"\") & "/_odata/v3.0-preview/WorkItems"]
),null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4,
Query=[
#"$filter"="contains(Area/AreaPath,'"&[Azure DevOps Area Path]&"') and WorkItemType eq 'Product Backlog Item'",
#"$select"="WorkItemId,Title",
#"$expand"="Iteration($select=IterationPath),Area($select=AreaPath)"
]
]

)
), 
#"Get Column Names"= Table.ColumnNames ( Table.Combine ( #"Retrieve oData"[FeedData] ) ),
#"Expand oData" = Table.ExpandTableColumn(#"Retrieve oData","FeedData",#"Get Column Names")
in 
#"Expand oData"

This gives an error once authenticated with Organisational Credentials

Wrapping Web.Contents in Binary.ToText

Binary.ToText(Web.Contents("https://analytics.dev.azure.com",
[
RelativePath=[Azure DevOps Organisation] & "/" & Text.BeforeDelimiter([Azure DevOps Area Path],"\") & "/_odata/v3.0-preview/WorkItems"]
))

   results in error

So my queston is - How can I rewrite the query to (if possible):

- Accept organisational credentials

- Enable the report to be refreshed once published

- Enable me to see the preview

Thanks in advance.

[ferryv · Accepted solution]

I found a way around the Dynamic Link issue with power query when connected to Azure DevOps with multiple organisations.

For reference, the issue is that the organisation in the main URL is required for Azure DevOps connection. 

As a workaround, the following will work - and enables refreshing - until a better solution is in place (I hope this will help others with similar issues also):

• Have a table with the organisations, area paths and index column (starting at 1 in my case)
• Have a function that combines the web.content feeds and expad everything
• A second function that deals with the web.content feed itself (I split them out so I can call this function for different functions.

Organisation & Area Path information

The table is called "Input" for the purposes of this explanation:

let
Source = Table.Combine({
Table.FromRecords({[Organisation = "OrgA", AreaPath = "Area1\Path1", Project = "Project1"]}),
Table.FromRecords({[Organisation = "OrgB", AreaPath = "Area2\Path2", Project = "Project1"]})
}),

#"Added Index" = Table.AddIndexColumn(Source, "ADO Id", 1, 1, Int64.Type)
in
#"Added Index"

Change, Add, Remove organisations, area paths, etc as applicable. It should be possible to connect it to an external source, like Excel or similar, but "... references other queries or steps, so it may not directly access a data source. Please rebuild this data combination." errors occur and I have not been able to resolve this yet.

The function that combines and unpacks the info 

The function is called "Unpack" for the purposes of the explanation:

(org as list, project as list, areaPath as list, index as list,optional filters as text)=>
let
fieldSelection = "WorkItemId,Title,State,OriginalEstimate,ParentWorkItemId,"
&"CreatedDate,ActivatedDate,StateChangeDate,ClosedDate,WorkItemType,TagNames,"
&"ChangedDate,CycleTimeDays,LeadTimeDays,StartDate,TargetDate",

filters = if filters = null then "" else filters,
#"Retrieve data"= 
List.Generate(()=>[i=List.Min(index)-1], each [i] <List.Max(index),each [i = [i]+1], each 
Table.FromRecords(
{

[

FeedData= Feed(org{[i]},project{[i]},areaPath{[i]},fieldSelection,filters),
Organisation = org{[i]},
AdoId = index{[i]}
]})), 

#"Converted to Table" = Table.FromList( #"Retrieve data", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Extract FeedData Column" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"FeedData", "Organisation", "AdoId"}, {"FeedData", "Organisation", "ADO Id"}),
#"Extract FeedData Column Records" = Table.ExpandRecordColumn(#"Extract FeedData Column", "FeedData", { "value"}, { "FeedData.values"}),
#"Expand FeedData.values Lists" = Table.ExpandListColumn( #"Extract FeedData Column Records", "FeedData.values"),
#"Remove Empty Record options" = Table.SelectRows( #"Expand FeedData.values Lists", each ([FeedData.values] <> null)),
#"Get Column Names"= Record.FieldNames ( Record.Combine ( #"Remove Empty Record options"[FeedData.values] ) ),
#"Expand Records" = Table.ExpandRecordColumn( #"Expand FeedData.values Lists", "FeedData.values", #"Get Column Names"),
#"Expand Area Path" = Table.ExpandRecordColumn(#"Expand Records", "Area", {"AreaPath"}, {"Area Path"}),
#"Expand Project Name" = Table.ExpandRecordColumn(#"Expand Area Path", "Project", {"ProjectName"}, {"Project Name"}),
#"Expand AssignedTo" = Table.ExpandRecordColumn(#"Expand Project Name", "AssignedTo", {"UserName"}, {"User Name"}),
#"Expand Iteration Path" = Table.ExpandRecordColumn(#"Expand AssignedTo" , "Iteration",{"IterationPath", "StartDate", "EndDate"}, {"Iteration Path", "Iteration Start Date", "Iteration End Date"})

in
#"Expand Iteration Path"

 Some things of note in this function:

• org, areaPath and index are linked to the respective columns in the "Input" table (above)
• an Optional "filters" parameter is added to enable e.g. filtering by workItemType (eg WorkItemType eq 'Initiative' to only return a subset of work items)
• fieldSelection is added as a variable in this function on purpose, so it is easier to manage the fields that are returned and the function can be clones and appended to create a new function that retrieves different data 
• The line "FeedData= Feed(org{[i]},areaPath{[i]},fieldSelection,filters)" calls the next function, called Feed.   

 The function that retrieves the actual content

The function is called "Feed" for the purposes of the explanation:

(org as text, project as text,areaPath as text, fieldSelection as text, filters as text)=>
let
Source = if org = "OrgA" then Json.Document( Web.Contents ("https://analytics.dev.azure.com/OrgA/_odata",[
RelativePath = "v3.0-preview/WorkItems?",
Query=[#"$filter"="(Area/AreaPath eq '"&areaPath&"' and Project/ProjectName eq '"& project &"' "& filters&")",
#"$select"= fieldSelection,
#"$expand"="Iteration($select=IterationPath,StartDate,EndDate),"
&"Area($select=AreaPath),"
&"AssignedTo($select=UserName),"
&"Project($select=ProjectName),"
]])) else

Json.Document( Web.Contents ("https://analytics.dev.azure.com/OrgB/_odata",[
RelativePath = "v3.0-preview/WorkItems?",
Query=[#"$filter"="(Area/AreaPath eq '"&areaPath&"' and Project/ProjectName eq '"& project &"' "& filters&")",
#"$select"= fieldSelection,
#"$expand"="Iteration($select=IterationPath,StartDate,EndDate),"
&"Area($select=AreaPath),"
&"AssignedTo($select=UserName),"
&"Project($select=ProjectName),"
]]))
in
Source

Some things of note:

• The dynamic source issue seems to appear when the base URL in Web Content is a variable. So to get around this, an if statement is added to check the value of "org" (linked to the Organisation in the "Unpack" function) and based on that value, a new web.contents function is defined with a static URL. Not perfect, but it seems to work.
• While in most documentation, the URL is to be defined as "https://analytics.dev.azure.com/{Organisation}/{Project}/_odata/...", the organisation can be added to the query string also via " Project/ProjectName eq '{ProjectName}' " (the documentation typically refers to ProjectSK instead, but cannot be easily found by an end user.
• If new organisations are to be added, append the if statement. The new URL needs to be authenticated, so the right permissions in Azure DevOps need to be set.

The output table

For the purpose of this explanation, it's called "Result"

let
Source = Unpack(Input[Organisation], Input[Project], Input[AreaPath], Input[#"ADO Id"]," and WorkItemType eq 'Initiative'")
in
Source

You call the "Unpack function", link this to the "Input" table columns and add the filter info (here: " and WorkItemType eq 'Initiative'"). You can add any number of tables as needed in the same way

[lbendlin]

have you tried folding your query into the original one instead of using Table.AddColumn as the first step?

[ferryv]

An example what I am trying to achieve:

I have a PowerBi table (ADO), which can be from Excel, a Sharepoint list or hardcoded. For example:

let
Source = Table.Combine({
Table.FromRecords({[Organisation = "ado-orgA", AreaPath = "Project1\Sandbox"]}),
Table.FromRecords({[Organisation = "ado-orgB", AreaPath = "Project2/Sandbox"]})
}),

#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type)
in
#"Added Index"

 Each entry in the table is a separate Azure DevOps organisation and Area Path. The base URL for the feed or web content is: "https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItems?" and the project can be derived from the area path (first path of the area path (e.g. Project1).

The organization and project appear to make the the URL a dynamic source and oData.Feed does not appear to be able to resolve this. So I tried the web.contents option by creating a function (Retrieve oData) and loop through the table.

(org as list, areapath as list,index as list)=>

let
#"Retrieve data"= 
List.Generate(()=>[i=List.Min(index)-1], each [i] <List.Max(index),each [i = [i]+1], each Table.FromRecords(
{

[
FeedData =Json.Document( Web.Contents ("https://analytics.dev.azure.com/",[
RelativePath = org{[i]} &"/"
& Text.BeforeDelimiter(areapath{[i]},"\")
& "/_odata/v3.0-preview/WorkItems?",
Query=[#"$filter"="contains(Area/AreaPath,'"&areapath{[i]}&"')",
#"$select"="WorkItemId,Title,State,OriginalEstimate,ParentWorkItemId,"
&"CreatedDate,ActivatedDate,StateChangeDate,ClosedDate,"
&"WorkItemType,TagNames,ChangedDate,CycleTimeDays,"
&"LeadTimeDays,StartDate,TargetDate",
#"$expand"="Iteration($select=IterationPath,StartDate,EndDate),"
&"Area($select=AreaPath),"
&"AssignedTo($select=UserName)"
]])),
Organisation = org{[i]},
AdoId = index{[i]}
]}))

in
#"Retrieve data"

 the org, area path and index variables are linked to the respective table columns in ADO. And once linked, invoked and converted, I see the correct data in the PowerBI Desktop application.

I use the function as the intent is to create tables with additional filters (filter variable omitted in this instance). Also omitted the conversion, etc from the above code for clarity.

However, the problem arises in this case when uploading it to a workspace as the report tries to authenticate against https://analytics.dev.azure.com/, and this does not seem to allow me to add my organisational credentials.

Anonymous does not enable refresh here, and usng Basic authentication results in 

When adding the org in the main url instead of the relative path, the option to connect via an organisational account appears, but is not supported:

When using oData.Feed instead of Web.Contents, I can connect via organisational credentials, but a Dynamic source error is shown

[ferryv]

Note. When ading the org, etc in the main url (as shown below), the dynamic source error from above also occurs.:

(org as list, areapath as list,index as list)=>

let

#"Retrieve data"= 
List.Generate(()=>[i=List.Min(index)-1], each [i] <List.Max(index),each [i = [i]+1], each Table.FromRecords(
{

[
FeedData =Json.Document( Web.Contents ("https://analytics.dev.azure.com/"&org{[i]} &"/"
& Text.BeforeDelimiter(areapath{[i]},"\")
& "/_odata/",[
RelativePath = "v3.0-preview/WorkItems?",
Query=[#"$filter"="contains(Area/AreaPath,'"&areapath{[i]}&"')",
#"$select"="WorkItemId,Title,State,OriginalEstimate,ParentWorkItemId,"
&"CreatedDate,ActivatedDate,StateChangeDate,ClosedDate,"
&"WorkItemType,TagNames,ChangedDate,CycleTimeDays,"
&"LeadTimeDays,StartDate,TargetDate",
#"$expand"="Iteration($select=IterationPath,StartDate,EndDate),"
&"Area($select=AreaPath),"
&"AssignedTo($select=UserName)"
]])),
Organisation = org{[i]},
AdoId = index{[i]}
]}))

in
#"Retrieve data"

[ferryv]

I checked query folding, but didn't allow it.

I also tried looping through a list of URLs (both generated from a table or typed in) and using Query parameters. Looping done via list.generate. All work in desktop, but fail to refresh in powerbi.com due to dynamic query errors.

Seems the only option is to create a table per odata feed/web contents (with hard coded fully resolved URLs) and merge the tables afterwards, which works, but doesn't seem very efficient.

Any other suggestions welcome. 🙂

[ferryv]

Not sure what you mean by "the original one"? Do you mean the step prior to Table.AddColumn? Not familiar with query folding aside frm what I read to date.

How would I achieve it (am fairly new to M). I would need to loop through a table to find the organisation and area path info and append this to a standard Azure DevOps feed URL (https://analytics.dev.azure.com) as per https://docs.microsoft.com/en-us/azure/devops/report/powerbi/odataquery-connect?view=azure-devops, but the problem is that the org and area (first part of an area path) are dynamic and not part of the query string. 

This seems to be causing the problem with the oData feed. The Web.Contents function wants to authenticate, but needs the Azure DevOps org to be able to do this (which is dynamic) and the VSTS.AccountContents function throws a "Null value for ResourcePath" error when tryingto authenticate.