Results 1 to 6 of 6

Thread: Summarize selective columns in power query

  1. #1
    Neophyte sailepaty's Avatar
    Join Date
    Mar 2011
    Location
    Dallas, TX
    Posts
    4
    Articles
    0

    Summarize selective columns in power query



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

    I created a custom column with the following formula.

    = Table.AddColumn(#"Added Custom", "Custom", each [pP01]+[pP02]+[pP03]+[pP04]+[pP05]+[pP06])

    Is there a way to pass the fields that I want to add? For example if I just want to add [pP02]+[pP03]

    Something like this
    = Table.AddColumn(#"Added Custom", "Custom", each [pP02]+[pP03])

    Or like this
    = Table.AddColumn(#"Added Custom", "Custom", each [pP01]*1+[pP02]*1+[pP03]*0+[pP04]*0+[pP05]*0+[pP06]*0)

    Thanks in advance.

  2. #2
    Neophyte sailepaty's Avatar
    Join Date
    Mar 2011
    Location
    Dallas, TX
    Posts
    4
    Articles
    0
    I posted same question in the following link

    http: // www . mrexcel . com / forum / power-bi / 846491-summarize-selective-columns-power-query . html # post4120520

    Thanks

    PS. I had to insert spaces in the link because the forum didnít allow me to post it as a link

  3. #3
    Excel 2010 with free Power Query Add-In.
    Compatible with Office 2013 Pro Plus.
    Not at all like Bill's.
    http://www.mediafire.com/view/a9a7pl.../04_04_15.xlsx

  4. #4
    Neophyte sailepaty's Avatar
    Join Date
    Mar 2011
    Location
    Dallas, TX
    Posts
    4
    Articles
    0
    Hi Herb, by looking your code I just learned how to use other tables rows without having to merge the tables. Base on that, the below code gives me what I'm looking for.

    Code:
    let
        tP = Excel.CurrentWorkbook(){[Name="TableP"]}[Content],
        SourceTable = Excel.CurrentWorkbook(){[Name="TableS"]}[Content],
        AddTotP = Table.AddColumn(SourceTable, "TotP", each [P01]*tP[v01]{0}+[P02]*tP[v02]{0}+[P03]*tP[v03]{0}+[P04]*tP[v04]{0}+[P05]*tP[v05]{0}),
        AddTotC = Table.AddColumn(AddTotP, "TotC", each [C01]*tP[v01]{0}+[C02]*tP[v02]{0}+[C03]*tP[v03]{0}+[C04]*tP[v04]{0}+[C05]*tP[v05]{0}),
        SelCols = Table.SelectColumns(AddTotC,{"name", "TotP", "TotC"})
    in
        SelCols

    Thanks for your help

  5. #5
    Very elegant.
    Mighty nice of you to list crossposts at other forums.
    Now don't forget to do the same at the other forums.

  6. #6
    Neophyte sailepaty's Avatar
    Join Date
    Mar 2011
    Location
    Dallas, TX
    Posts
    4
    Articles
    0
    I included the link to this post when I first asked in Mr. Excel.

    Thanks

Posting Permissions

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