Power Query - Group by combined with FILTER

[tonijj]

Hi all,

Been searching for a solution for my issue for some time, but its time to ask for some expertise 🙂

WHAT I want to achieve - I want to take a dataset and sort out by whats "in scope" and then group and summarize only the values per order number. 

Current Data set

Order Number

CN Code - Its an EU code, or a material code if you like

Item Value - per line item

In scope - predetermined from an Excel

So, I want to basically want to summarize ONLY the items "In Scope", PER order number.

Wanted output ought to be:

Problem: 

When I Group By in Power Query on "Order Number" - the previous step "Filter by IN SCOPE" is ommited and I cant seem to get around it. 

Any help is appreciated. 

[Ahmedx]

pls try this

[tonijj]

Hi @Ahmedx 

Any chance you can share the steps or the pbix for me to look at? Sincerely 🙂 

[Ahmedx]

I posted the file pbix in the first post
and the video is in the second post

[Ahmedx]

to know how to do this watch my video

343.mp4

[amitchandak]

@tonijj , You can use a measure in visual, no need to group data in the Power query

calculate(Sum(Table[Item value]), filter(Table, Table[In Scope] = "X"))