Forum Discussion

MichaelK27's avatar
MichaelK27
New Member
3 years ago

Power Query sum with exceptions

Hi,

 

I have data in a row in this kind of format:

Name1, amount, name2, amount, name3, amount, name4, amount, name5, amount

 

Now I want to add a custom column where the output is the sum of the totale amount in the row. BUT if any of the names is e.g. School, I don't want to add this to my sum. However, in every row it can be in another place.

 

Is there a solution for this?

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi MichaelK27 

     

    Will you have always these number of columns? Or the number of columns could be dinamic?

  • MichaelK27's avatar
    MichaelK27
    New Member
    3 years ago

    Hi Anonymous ,

     

    I will have a static table with this information. And the total amount of columns used for this will be 9x the name (type of cost) and 9x the cost. Does this answer your question?

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi MichaelK27 ,

     

    It is recommended that you duplicate them, get each separate name and amount column, and append them together.

    Append queries - Power Query | Microsoft Learn

     

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.