Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Multiple SUMIFS on column

[ Spoiler ]  
Need Help
Tips and Tricks
  • BA_Pete's avatar
    BA_Pete
    5 years ago

    Anonymous ,

     

    Great news, happy to help 🙂

     

    Please accept the answer as the solution. This will help others with the same problem to find the solution quicker.

     

    Thanks,

     

    Pete

BA_Pete's avatar
BA_Pete
Super User
5 years ago

Hi Anonymous ,

1) In Power Query either go to your sales table or reference your sales table and add this new custom column:

 

personList = {[director], [manager], [rep]}

 

2) Expand this column to New Rows

3) Use your new [personList] field with [sales] to get your required output:

Here is the M code that you can copy and paste into a new blank query if you want to see it step-by-step:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hY7NCsJADITfJecepNt2fZeyh7BEjWy3un/g25ssCFIQL5kJ84XMusIDa4ABKLEXuSSMnrJH8eMJ3HAEGu+BStF8Mn/yeelA4KxrokgiG5YbUxVnl3n6BiSRmQs15czn/I7+WSlr9AqNqT9JV47dml+M1pIiaq09g3Nv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [director = _t, manager = _t, rep = _t, sales = _t]),
    addPersonList = Table.AddColumn(Source, "personList", each {[director], [manager], [rep]}),
    expandPersonList = Table.ExpandListColumn(addPersonList, "personList"),
    chgAllTypes = Table.TransformColumnTypes(expandPersonList,{{"director", type text}, {"manager", type text}, {"rep", type text}, {"sales", Int64.Type}, {"personList", type text}})
in
    chgAllTypes

 

Pete