Dynamic link issue

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