Power Query - Filter on multiple conditions

[Anonymous]

i am trying to limit the amount of data in Powerbi when i connect to my work's VSTS/AzureDevOps instance

if i connect all work item types i am hitting a timeout problem. lots of data. approx 2mil rows of data

i have filtered out specific workitem types from the load which has dropped my data by 2/3 which now is around 700,000 rows (as per Filter 1 below)

i still have a timeout issue every other day as it takes longer than 2 hours to load

i wish to add another filter/step to filter out more data based on IF conditions

Filter 1 (this is currently in place and filters out workitem types i do not care about)

= Table.SelectRows(#"3fa8b188-dae6-419f-8030-ed9d1e421736_Table", each ([Work Item Type] <> "Test Case" and [Work Item Type] <> "Test Plan" and [Work Item Type] <> "Test Suite" and [Work Item Type] <> "Task"))

Filter 2 (what i want to then apply is)

If workitem type is Bug or Defect, then give me everything

If a workitem type is a PBI, Support, Feature workitem, then give me everything that is from Created date 01/01/2019 onwards. specifically i wouldnt mind a rolling 18months. if that is possible

any help?

thanks

[arsaveli]

Which entity are you querying in DevOps? Do you actually have 2 million work items, or are you querying WorkItemSnapshot?

FYI WorkItemSnapshot is not designed to be exported like this - you have to aggregate your data for consumption.

You can create the filter you are talking about in the odata query you are running - if you are using analytics views, you can create two views and do a UNION on them in PowerBI.

[Anonymous]

@arsaveli 

thanks for the reply

im taking in the Workitems - history by month data

long story short, ive spoken to PowerBi team in MS and odata will not suit my needs.

been informed that ill need to use the "Azure DevOps (beta)" connector

im happy to be told otherwise, its what ive been discussing with MS. ive been through beta and preview testing

anyhow. possibly ill create different connections to the data. and filter out rule as needed

any other thoughts?