Incomplete export to PBI: Items without matching links filtered out when selecting "Return all top level items"

[nataliabernardo]

Hi, when I select “Return all top level items”, the query results in all top level items correctly, however when exporting to PBI, it filters out the use cases without linked work items.

Query: image.png

Thanks, Natalia

[gregdvorkin]

I have the same issue. Big disappointment. The project itself is great - there is no other way to get linked records from Azure DevOps in this format (the only right format BTW - both source and target in one row). If it would be a way to combine 2 queries in one Power BI report, it would solve the problem, but...

[CHohnbaum]

We ran into this issue with my team and I think I fixed it locally through a function update:

Update for just the WiqlRunOneHopWorkItemLinkQuery function (replace it via advanced editor in Power Query, starts at line 283 in the Functions definition).

• WiqlRunOneHopWorkItemLinkQuery_Only.txt

Update if you want to just replace the entire text of Functions without tracking down the one specific function that needs an update (this still just applies changes to a single function, the WiqlRunOneHopWorkItemLinkQuery function).

• Wiql.Functions.Updated.txt

Fix involved separately defining the Source ID table and the Target ID table, and then re-doing the join between them, to remove several steps that would cause Source to be filtered out if no Target (i.e. linked item) was present.

Here's the contents of the function I updated:

WiqlRunOneHopWorkItemLinkQuery = (contents as function, url as text, scope as record, query as record, optional options as record) as table =>
        let
            #"Check queryType" = if query[queryType]? = "oneHop" and query[queryResultType]? = "workItemLink"
                then query
                else error Error.Record("Error", 
                    "Query was updated and its configuration no longer matches ""Work items and direct links"""
                    & " and ""Return all top level items"" or ""Only return items that have matching links""."
                    & " Please generate this file again."),

            #"Get relations" = #"Check queryType"[workItemRelations],
            #"Convert to table" = Table.FromList(#"Get relations", Splitter.SplitByNothing(), { "Record" }, null, ExtraValues.Error),
            #"Expand relation" = Table.ExpandRecordColumn(#"Convert to table", "Record", {"rel", "target", "source"}, {"Link Type", "target", "source"}),
            #"Expand target" = Table.ExpandRecordColumn(#"Expand relation", "target", {"id"}, {"Target ID"}),
            #"Expand source" = Table.ExpandRecordColumn(#"Expand target", "source", {"id"}, {"Source ID"}),
            #"Relations Table" = Table.SelectRows(#"Expand source", each [Target ID] <> null),

            #"Replaced Value" = Table.ReplaceValue(#"Relations Table",null,each [Target ID],Replacer.ReplaceValue,{"Source ID"}),
            #"Select relations" = Table.ReplaceValue(#"Replaced Value",each [Source ID], null,Replacer.ReplaceValue,{"Target ID"}),

            #"Get fields" = List.Distinct(
                { "System.Id", "System.Title", "System.WorkItemType" }
                & List.Transform(#"Check queryType"[columns], each [referenceName])),
            #"Get Target IDs" = List.Distinct(#"Select relations"[Target ID]),
            #"Get Target ID table" = GetWorkItemFieldValues(contents, url, scope, #"Get Target IDs", #"Get fields", options),
            #"Get Source IDs" = List.Distinct(#"Select relations"[Source ID]),
            #"Get Source ID table" = GetWorkItemFieldValues(contents, url, scope, #"Get Source IDs", #"Get fields", options),

            #"Join by Relations" = Table.Join(#"Select relations", "Target ID", Table.PrefixColumns(#"Get Target ID table", "Target"), "Target.ID", JoinKind.LeftOuter),
            #"Remove Null Relations" = Table.SelectRows(#"Join by Relations", each ([Target.ID] <> null)),
            #"Join by Source to Target" = Table.Join(#"Remove Null Relations", "Source ID", Table.PrefixColumns(#"Get Source ID table", "Source"), "Source.ID", JoinKind.FullOuter),

            #"Remove duplicate columns" = Table.RemoveColumns(#"Join by Source to Target",{"Target ID", "Source ID"}),
            #"Define name conversion" = List.Transform(Table.ColumnNames(#"Remove duplicate columns"), 
                each { _, Text.Replace(Text.Replace(_, "Source.", "Source "), "Target.", "Target ")}),
            #"Apply name conversion" = Table.RenameColumns(#"Remove duplicate columns", #"Define name conversion")
        in
            #"Apply name conversion",