Forum Discussion

tonijj's avatar
tonijj
Icon for Helper IV rankHelper IV
2 years ago

Power Query - Group by combined with FILTER

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. 

Need Help

5 Replies

    • tonijj's avatar
      tonijj
      Icon for Helper IV rankHelper IV
      2 years ago

      Hi Ahmedx 

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

      • Ahmedx's avatar
        Ahmedx
        Icon for Super User rankSuper User
        2 years ago

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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    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"))