Summarize selective columns in power query

[sailepaty]

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.

PS. I made the same question in Excelguru forum.
http://www.excelguru.ca/forums/showthread.php?4312-Summarize-selective-columns-in-power-query

 

[ImkeF]

Your second thought looks very promising. So you need an additional table that contains the "valid" columns per line of your original table (name your cols mP01, mP02... and fill in "1" if valid). Join that on the key and you'll get these as additional columns in your original table.

Then your formula will just be sth like: = Table.AddColumn(#"Added Custom", "Custom", each [pP01]*[mP01]+[pP02]*[mP02]...

(Well, of course you could use a grouping attribute as well, if your logic is not on a line level but swhere else)

But I'd wait for Billszysz to come by here, he'd probably deliver some great formula, that does that fully dynamic (irrespective of how many columns you have )

stay querious

 

[sailepaty]

ImkeF, your solution should work. However, like you said let’s wait if Billszysz or somebody else comes with a dynamic solution.

Thanks a lot for your help.

 

[billszysz]

Hi sailepaty, Hi ImkeF
haha...billszysz is comming
Ok.... so, this is only first attempt.

let
    ParamTable = Excel.CurrentWorkbook(){[Name="tblParam"]}[Content],
    SourceTable = Excel.CurrentWorkbook(){[Name="tblSource"]}[Content],
    AddIndex = Table.AddIndexColumn(SourceTable, "Indeks", 0, 1),
    UnPivotCloumns = Table.UnpivotOtherColumns(AddIndex, {"name", "Indeks"}, "ColName", "Value"),
    MerCol1 = Table.NestedJoin(UnPivotCloumns,{"ColName"},ParamTable,{"ColName"},"New"),
    ExpNewMult = Table.ExpandTableColumn(MerCol1, "New", {"Multiply"}, {"New.Multiply"}),
    FilterNotNull = Table.SelectRows(ExpNewMult, each ([New.Multiply] <> null)),
    AddCol = Table.AddColumn(FilterNotNull, "Custom", each [Value]*[New.Multiply]),
    GroupRows = Table.Group(AddCol, {"name", "Indeks"}, {{"Sum", each List.Sum([Custom]), type number}}),
    MergeTable = Table.NestedJoin(AddIndex,{"name","Indeks"},GroupRows,{"name","Indeks"},"Custom"),
    ExpCustom = Table.ExpandTableColumn(MergeTable, "Custom", {"Sum"}, {"Sum"}),
    RemCol = Table.RemoveColumns(ExpCustom,{"Indeks"})
in
    RemCol

And here (below) is a link to an example file on my google drive
https://drive.google.com/file/d/0B6UlMk8OzUrxRmNSb2s1ei1BMm8/view?usp=sharing

Regards ... and sq

 

[sailepaty]

Hi billszysz,

I have something similar set, but not as clean as your sample. I was looking for a different approach because I have 48 different fields to consider and was trying to avoid the second table.

Thanks a lot for your help.

 

[ImkeF]

OK, fair enough. So in order to develop a different approach, it would be helpful to know:

1) How you currently express the logic to be applied
2) How you'd prefer that to be transfered to PQ or referenced by PQ.
3) Will there be different logics to be applied for one table (so for some lines [pP02]+[pP03] and for others [pP03]+[pP04]+[pP05] aso)?

 

[sailepaty]

Hi guys, merging your ideas this is what I have so far.

Parameters Table

v01 v02 v03 v04 v05 0 1 1 0 0 <tbody> </tbody>
Source Table											Totals Table
name	P01	P02	P03	P04	P05	C01	C02	C03	C04	C05		name	TotP	TotC
John Doe	5	6	4	6	10	10	8	3	5	8		John Doe	10	11
Imke	9	7	10	9	1	6	3	10	10	3		Imke	17	13
Bill	2	5	7	7	10	9	3	7	1	2		Bill	12	10
Mike	1	3	3	1	9	5	7	8	6	6		Mike	6	15
Miguel	2	5	3	3	5	7	8	4	3	2		Miguel	8	12
Chris	3	8	7	6	5	3	7	10	7	4		Chris	15	17
Ken	4	1	4	9	7	1	6	10	4	8		Ken	5	16
Kurt	6	5	9	1	10	3	6	2	2	7		Kurt	14	8
Bill	3	10	4	5	8	8	9	9	8	3		Bill	14	18

<tbody>
</tbody>

let
    ParamTable = Excel.CurrentWorkbook(){[Name="tblParam"]}[Content],
    AddParDig = Table.AddColumn(ParamTable, "ParDig", each 1),
    SourceTable = Excel.CurrentWorkbook(){[Name="tblSource"]}[Content],
    AddSourceDig = Table.AddColumn(SourceTable, "SouDig", each 1),
    MerCols = Table.NestedJoin(AddSourceDig,{"SouDig"},AddParDig,{"ParDig"},"New"),
    ExpNewCol = Table.ExpandTableColumn(MerCols, "New", {"v01", "v02", "v03", "v04", "v05"}, {"v01", "v02", "v03", "v04", "v05"}),
    AddTotP = Table.AddColumn(ExpNewCol, "TotP", each [P01]*[v01]+[P02]*[v02]+[P03]*[v03]+[P04]*[v04]+[P05]*[v05]),
    AddTotC = Table.AddColumn(AddTotP, "TotC", each [C01]*[v01]+[C02]*[v02]+[C03]*[v03]+[C04]*[v04]+[C05]*[v05]),
    SelCols = Table.SelectColumns(AddTotC,{"name", "TotP", "TotC"})
in
    SelCols

Any suggestions you may have will be really appreciated.

Thanks

 

[billszysz]

No problem, sailepaty

let
    ParamTable = Excel.CurrentWorkbook(){[Name="tblParam2"]}[Content],
    UnPivotParam = Table.UnpivotOtherColumns(ParamTable, {}, "Attribute", "Value"),
    SplitCol = Table.SplitColumn(UnPivotParam,"Attribute",Splitter.SplitTextByPositions({0, 1}, false),{"Attribute.1", "Key"}),
    RemoveColumn = Table.RemoveColumns(SplitCol,{"Attribute.1"}),
    ReadyParam = Table.SelectRows(RemoveColumn, each [Value] <> 0),
    SourceTable = Excel.CurrentWorkbook(){[Name="tblSource2"]}[Content],
    AddIndexSource = Table.AddIndexColumn(SourceTable, "Indeks", 0, 1),
    UnPivotSource = Table.UnpivotOtherColumns(AddIndexSource, {"name","Indeks"}, "Attribute", "Value"),
    ReadyToMerge = Table.SplitColumn(UnPivotSource,"Attribute",Splitter.SplitTextByPositions({0, 1}, false),{"Attribute", "KeySource"}),
    MergeBoth = Table.NestedJoin(ReadyToMerge,{"KeySource"},ReadyParam,{"Key"},"New"),
    ExpandTable = Table.ExpandTableColumn(MergeBoth, "New", {"Value"}, {"New.Value"}),
    FilterNewValue = Table.SelectRows(ExpandTable, each ([New.Value] <> 0 and [New.Value] <> null)),
    AddColumn = Table.AddColumn(FilterNewValue, "Custom", each [Value]*[New.Value]),
    RemoveColumns = Table.RemoveColumns(AddColumn,{"KeySource", "Value", "New.Value"}),
    GroupingRows = Table.Group(RemoveColumns, {"name", "Attribute","Indeks"}, {{"Sum", each List.Sum([Custom]), type number}}),
    PivotAttrib = Table.Pivot(GroupingRows, List.Distinct(GroupingRows[Attribute]), "Attribute", "Sum", List.Sum),
    SortByIndex = Table.Sort(PivotAttrib,{{"Indeks", Order.Ascending}}),
    RemIndex = Table.RemoveColumns(SortByIndex,{"Indeks"}),
    TheEnd = Table.RenameColumns(RemIndex,{{"P", "Total P"}, {"C", "Total C"}})
in
    TheEnd

tblParam2 is your parameters Table and tblSource2 is your Source Table.

Happy Easter

 

[sailepaty]

Thanks Bill.

Happy Easter to you too.