Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Power Query - Filter on multiple conditions

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

 

 

 

 

Need Help

2 Replies

  • arsaveli's avatar
    arsaveli
    Helper I
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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?