Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table - [WorkItems] - 245 000 records, 90 MB original size - which is part of PBI Report.
It contains 30 API references (pulling data directly from API - 30 OData Feeds (Projects)).
Code written in PowerQuery (OData)
When I am refreshing this PBI report, the [WorkItems] table keeps failing with the following msg -
(The Refresh time is over 10 min)
I was able to lower [WorkItems] tbl size by removing about half of the API calls (OData feeds) - left 12 out of 30.
Then the tbl size reduced to 42MB, and it's now about 100 000 records and refresh is 2 min
(3 min when I publish it on PBI Services; scheduled refresh is now not failing).
But - my problem - I need to keep ALL 30 OData Feeds (Projects)...
To achieve this:
- Are there working and more or less quick ways to reduce this tbl size?
- Should I add some sort of parameter(s) into Data Source settings - in order to speed up the refresh
(to load my records in parts, quicker)?
- One other possible option is to add [Date] filter to reduce a tbl size - [Current Date] - 24 months
I am allowed to do this. (Not sure where in the code and how exactly)?
Here is the code (PowerQuery, OData, I included only 3 out of 30 OData feeds):
Any suggestions would be very helpful
// "$select=ParentWorkItemId, StoryPoints, State, WorkItemType, Title, IterationSK, AreaSK, WorkItemId" & "&$filter=(WorkItemType eq 'Bug' or WorkItemType eq 'User Story')", null, [Implementation="2.0"]),
let
Source = OData.Feed("https://analytics.dev.azure.com/MyCompany/Research and
Development/_odata/v3.0-preview/WorkItems?" &
"$select=ParentWorkItemId, StoryPoints, State,
WorkItemType, Title, IterationSK, AreaSK, WorkItemId,
Area" & "&$filter=(WorkItemType eq 'Bug' or
WorkItemType eq 'User Story')" &
"&$expand=Area($select=AreaPath)", null,
[Implementation="2.0"]),
#"Add all Ops & CP projects" = Table.Combine({
Source,
OData.Feed("https://analytics.dev.azure.com/MyCompany/Cloud
Platform/_odata/v3.0-preview/WorkItems?" &
"$select=ParentWorkItemId, StoryPoints, State, WorkItemType,
Title, IterationSK, AreaSK, WorkItemId, Area" & "&$filter=
(WorkItemType eq 'Bug' or WorkItemType eq 'User Story')" &
"&$expand=Area($select=AreaPath)", null,
[Implementation="2.0"]),
OData.Feed("https://analytics.dev.azure.com/MyCompany/Batch
Management/_odata/v3.0-preview/WorkItems?" &
"$select=ParentWorkItemId, StoryPoints, State, WorkItemType,
Title, IterationSK, AreaSK, WorkItemId, Area" & "&$filter=
(WorkItemType eq 'Bug' or WorkItemType eq 'User Story')" &
"&$expand=Area($select=AreaPath)", null,
[Implementation="2.0"]),
}),
#"Add AreaPath" = Table.ExpandRecordColumn(#"Add all Ops & CP projects",
"Area", {"AreaPath"}, {"AreaPath"}),
// Calculate the date 24 months ago from the current date
Date24MonthsAgo = Date.AddMonths(DateTime.LocalNow(), -24),
// Filter data to include only records from the last 24 months
FilteredData = Table.SelectRows(ConvertedIDColumns, each
DateTime.From([CreatedDate]) >= Date24MonthsAgo),
#"Rename Story Points to Effort" = Table.RenameColumns(#"Add AreaPath",{{"StoryPoints", "Effort"}}),
#"Add Organization" = Table.AddColumn(#"Rename Story Points to Effort", "Organization", each "MyCompany"),
#"Change IDs to text" = Table.TransformColumnTypes(#"Add Organization",{{"WorkItemId", type text}, {"ParentWorkItemId", type text}}),
#"Make IDs unique" = Table.TransformColumns(
#"Change IDs to text",
{
{
"WorkItemId",
each
Text.Combine({(_),"-VSTS"}),
type text
}
}
),
#"Make Parent IDs unique" = Table.TransformColumns(
#"Make IDs unique",
{
{
"ParentWorkItemId",
each
Text.Combine({(_),"-VSTS"}),
type text
}
}
),
#"Replaced Value" = Table.ReplaceValue(#"Make Parent IDs unique","-VSTS","",Replacer.ReplaceValue,{"ParentWorkItemId"}),
#"Parent Orphans to ""No Feature""" = Table.ReplaceValue(#"Replaced Value","","No Feature",Replacer.ReplaceValue,{"ParentWorkItemId"})
in
#"Parent Orphans to ""No Feature"""
Reduce Table Size:
Optimize Query Execution:
Have you considered setting the Timeout parameter for the Odata.Feed ?
Tried that and it absolutely didnt help 😞
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
7 | |
5 | |
4 | |
2 | |
2 |