powerbi - Updating table in Power BI since last update using Power Query - Stack Overflow

admin管理员组

文章数量:1384794

I have a Power Query that pulls data from ADO from a fixed date. This results in a massive download of data. I am looking for a way to only download new records from the last date the query was run (ideally) or just going back a set number of day, and to append the table with the returned data.

This is the current query:

let
    Source = OData.Feed("https:......?$apply=filter((Team/TeamName eq 'DXC Scrum Team 1' or Team/TeamName eq 'DXC Scrum Team 2' ) and BoardName eq 'Evolve Requirements' and DateValue ge 2024-07-20Z)/groupby((DateValue, Team/TeamName ,Custom_BlockedStatus,State,ColumnName,LaneName,WorkItemID,ParentWorkItemID,WorkItemType,Title,CreatedDate,ActivatedDate,ResolvedDate,ClosedDate,ChangedDate,Custom_DefectSeverity,Custom_DIFRelease,Changedby/Username,CreatedBy/Username, AssignedTo/UserName,Area/AreaPath,Iteration/IterationPath),aggregate($count as Count))", null, [Implementation="2.0"]),
    #"Expanded Team" = Table.ExpandRecordColumn(Source, "Team", {"TeamName"}, {"Team.TeamName"}),
    #"Expanded Area" = Table.ExpandRecordColumn(#"Expanded Team", "Area", {"AreaPath"}, {"Area.AreaPath"}),
    #"Expanded AssignedTo" = Table.ExpandRecordColumn(#"Expanded Area", "AssignedTo", {"UserName"}, {"AssignedTo.UserName"}),
    #"Expanded ChangedBy" = Table.ExpandRecordColumn(#"Expanded AssignedTo", "ChangedBy", {"UserName"}, {"ChangedBy.UserName"}),
    #"Expanded CreatedBy" = Table.ExpandRecordColumn(#"Expanded ChangedBy", "CreatedBy", {"UserName"}, {"CreatedBy.UserName"}),
    #"Expanded Iteration" = Table.ExpandRecordColumn(#"Expanded CreatedBy", "Iteration", {"IterationPath"}, {"Iteration.IterationPath"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Iteration", "De-Duplication", each if [State] = "Completed" and
[DateValue] > Date.AddDays([ClosedDate],15) then "Exclude" else "Inculde"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([#"De-Duplication"] = "Inculde"))
in
    #"Filtered Rows"

Is there an option to say set the date filter to the last 2 days or week for example?

I have a Power Query that pulls data from ADO from a fixed date. This results in a massive download of data. I am looking for a way to only download new records from the last date the query was run (ideally) or just going back a set number of day, and to append the table with the returned data.

This is the current query:

let
    Source = OData.Feed("https:......?$apply=filter((Team/TeamName eq 'DXC Scrum Team 1' or Team/TeamName eq 'DXC Scrum Team 2' ) and BoardName eq 'Evolve Requirements' and DateValue ge 2024-07-20Z)/groupby((DateValue, Team/TeamName ,Custom_BlockedStatus,State,ColumnName,LaneName,WorkItemID,ParentWorkItemID,WorkItemType,Title,CreatedDate,ActivatedDate,ResolvedDate,ClosedDate,ChangedDate,Custom_DefectSeverity,Custom_DIFRelease,Changedby/Username,CreatedBy/Username, AssignedTo/UserName,Area/AreaPath,Iteration/IterationPath),aggregate($count as Count))", null, [Implementation="2.0"]),
    #"Expanded Team" = Table.ExpandRecordColumn(Source, "Team", {"TeamName"}, {"Team.TeamName"}),
    #"Expanded Area" = Table.ExpandRecordColumn(#"Expanded Team", "Area", {"AreaPath"}, {"Area.AreaPath"}),
    #"Expanded AssignedTo" = Table.ExpandRecordColumn(#"Expanded Area", "AssignedTo", {"UserName"}, {"AssignedTo.UserName"}),
    #"Expanded ChangedBy" = Table.ExpandRecordColumn(#"Expanded AssignedTo", "ChangedBy", {"UserName"}, {"ChangedBy.UserName"}),
    #"Expanded CreatedBy" = Table.ExpandRecordColumn(#"Expanded ChangedBy", "CreatedBy", {"UserName"}, {"CreatedBy.UserName"}),
    #"Expanded Iteration" = Table.ExpandRecordColumn(#"Expanded CreatedBy", "Iteration", {"IterationPath"}, {"Iteration.IterationPath"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Iteration", "De-Duplication", each if [State] = "Completed" and
[DateValue] > Date.AddDays([ClosedDate],15) then "Exclude" else "Inculde"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([#"De-Duplication"] = "Inculde"))
in
    #"Filtered Rows"

Is there an option to say set the date filter to the last 2 days or week for example?

• powerbi
• powerquery

Share Improve this question Follow asked Mar 19 at 1:57 SpionredSpionred 80722 gold badges1111 silver badges2828 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

It might not be the most ideal way, but I have cracked half the challenge by creating an identical table (history) with an Append step to add the rows from the table (staging) with the main query. Now when I edit the staging table query to just get the data from the date it was last run (manually entered), it them adds result to the history table.

Next step would be to figure out if there is a way to automatically update the date to the last time it was run.

本文标签： powerbiUpdating table in Power BI since last update using Power QueryStack Overflow