CODEWITHSUNDEEP
powerbidax
dougp
dougp

Reputation: 3089

Filter a table on the way in

Using Power BI to query Azure DevOps through the OData connector...

I see query definitions like = Source{[Name="WorkItems",Signature="table"]}[Data]. One of the columns is WorkItemType and contains values like Task, Sprint, Requirement, etc. Is there a way to modify the query definition so that it returns only Requirements and make that happen on the server to reduce the server resources used by the query?

Searching for power bi dax "= Source{" using a search engine does not produce meaningful results because search engines ignore punctuation. Likewise, since everything has a source, searching for power bi dax source is not helpful.

Upvotes: 0

Views: 918

Answers (1)

Murray Foxcroft
Murray Foxcroft

Reputation: 13745

In PowerBI, click:

  • Transform Data (a new window opens)
  • The WorkItemType column drop down
  • Check the items you wish to see

This generates a "M" language PowerQuery query for you which manipulates the data on the way "in" to PowerBI. If you right click the Query and select "Advanced Editor" you will see the "M" script. I use Excel as a source for demonstration but the principles are the same. You can edit and refine the query as you like in the advanced editor. See the "Filtered Rows" step in the below. 

let
    Source = Excel.Workbook(File.Contents("C:\Users\localadmin\Desktop\Sprint.xlsx"), null, true),
    #"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column2", "Column3", "Column4"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Sheet1", type text}, {"WorkItemType", type text}, {"Col 1", Int64.Type}, {"Col 2", Int64.Type}, {"Col 3", Int64.Type}, {"Sheet1_1", type text}, {"Sheet", type text}, {"false", type logical}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Sheet1"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([WorkItemType] = "Requirement"))
in
    #"Filtered Rows"

enter image description here

Upvotes: 1

Related Questions