0

I have quite a large dataset where I need to compile a column based on its cell values.

an example of the data is below:

enter image description here

I'm trying to:

  • Merge all rows in the column 'Product' that only have the value 'AccountsA'
  • The numerical values need to be summed for 'AccountsA' when merged
  • The 'Product_Type' values differ and can be replaced with 'A' when merged for 'AccountsA'

My first thought was to use an if statement but I don't know if this is possible when grouping in powerquery and I'm struggling to find any examples online.

The outcome I'm trying to achieve is shown below (using the above example):

enter image description here

Any help would be appreciated. Thank you.

Improve this question

1 Answer 1

Reset to default
1

Starting with this as Table1:

enter image description here

This code...

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Channel", type text}, {"Product_Type", type text}, {"Total Offered Net", Int64.Type}, {"Total Offered Inc", Int64.Type}, {"Total Handled", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product"}, {{"Channel", each [Channel]{0}}, {"Product_Type", each [Product_Type]{0}}, {"Total Offered Net", each List.Sum([Total Offered Net]), type nullable number}, {"Total Offered Inc", each List.Sum([Total Offered Inc]), type nullable number}, {"Total Handled", each List.Sum([Total Handled]), type nullable number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Product] = "AccountsA"),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([Product] <> "AccountsA")),
    #"Appended Query" = Table.Combine({#"Filtered Rows1", #"Filtered Rows"}),
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"Product", Order.Ascending}})
in
    #"Sorted Rows"

...gives me this result:

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.