Results 1 to 3 of 3

Thread: Operation on multiple rows with dynamic columns

  1. #1
    Neophyte theekin's Avatar
    Join Date
    Sep 2017
    Location
    Kyiv, UKRAINE
    Posts
    2
    Articles
    0

    Operation on multiple rows with dynamic columns



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi guys,


    Can anyone give me a hand?

    Suppose I have a table in Excel with the following information:

    Product A B С
    1 1,000 2,000 3,000
    2 1,000 2,000 3,000
    3 1,000 2,000 3,000

    and I need to perform an operation, let it be sum, on the multiple rows (from 1 to ...) which can be dynamic. And the sum function needs to include the newly added columns (which are also dynamic, for example, from A to ...).

    What would the code in Power Query be?

    Thank you in advance,

    Alex

  2. #2
    Conjurer Rudi's Avatar
    Join Date
    Jan 2014
    Location
    Cape Town, RSA
    Posts
    134
    Articles
    0
    Excel Version
    Excel 365 ProPlus
    Try this...

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"A", type number}, {"B", type number}, {"C", type number}}),
        #"Sum Except" = Table.AddColumn(#"Changed Type","Total", each List.Sum(Record.ToList(Record.SelectFields(_,List.RemoveItems(Table.ColumnNames(#"Changed Type"), {"Product"})))))
    in
        #"Sum Except"
    Regards,
    Rudi
    www.eileenslounge.com

  3. #3
    Neophyte theekin's Avatar
    Join Date
    Sep 2017
    Location
    Kyiv, UKRAINE
    Posts
    2
    Articles
    0
    Thank you, Rudi! That is a very nice way of summing rows.
    I managed to do this thru the merge function, but your way is much better.
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •