Power Query: M - is it possible to use List.Sum on a set of columns retrieved via List.Difference?

[Anonymous]

Hi All, As per message's subject, I have got an issue: After pivoting an attribute to get as many different fields as they happen to appear as values into the previous step under that attribute, I needed to dynamically replace the null values with a zero in this way, which actually work: ReplacedNull = Table.ReplaceValue(PivotedColumn, null, 0, Replacer.ReplaceValue, List.Difference(Table.ColumnNames(PivotedColumn), {"ID", "Name"})) i.e.: by simply leaving as 'fixed' the non-numeric fields, like the Employee ID and Employee Name - while replacing the null values across all of the others. Now, my issue is: Is there a way to 'automatically' sum up these fields (the ones which I replaced null with zero into) in order to avoid the hard-coding of their labels, being those of a variable nature (as you might have got, when a pivoting operation is performed)? I tried something with: InsertedSum = Table.AddColumn(ReplacedNull, "Total", each List.Sum(List.Difference(Table.ColumnNames(ReplacedNull), {"ID", "Name"})), type number) ...and it does not work, as it triggers this error: Expression.Error: We cannot apply operator - to types Text and Text. Details: Operator=- Left=Archive Right=Archive Anyone of goodwill who knows how to do that? Many Thanks!

[fra123]

Hi There,

I am trying to replicate a simple Excel Step in Power Query, but getting into meny errors

Have a table wti the Following column: "P", "D", "S","N"

I want to add a new colums 

Custom= Sum( P- (D+S) - N)

I have been trying wit List.Sum but I can't find the right sysntax. Hope you can help. I am not very confident with the Query Editor

[karimm]

I don't think you need List.Sum at all.

Your fomula should just be [P]-([D]+[S]) - [N]

[fra123]

Thank you Karimm, you are 100% right! Thanks a lot

[Anonymous]

Thank you so much Imke!

You are perfeclty right and that perfectly works.

Again, here are my warmest thanks!

[Anonymous]

Hi!

Thank you so much for your kindness.

All that I can share is this example with dummy data, but it exactly represents the issue.

Starting from a situation like the following (let it be our "Source"):

ID             Name  Column 1  Column 2  Column 3  Column 4
Q25019    XXX      46             109            11             3
Q25024    YYY      59             333            55             92
Q25030    ZZZ      27             208            29             108
Q25032    AAA     6               62              10             8
Q25039    BBB      3               136            null           2
Q25040    CCC     2                26             6               null
Q25043    DDD    3                67             null           null
Q25055    EEE      13              71             18             2

I replaced the null values with the first formula, above, in order to avoid to hard-code the field labels (they may change):

ReplacedNull = Table.ReplaceValue(Source, null, 0, Replacer.ReplaceValue,
List.Difference(Table.ColumnNames(Source), {"ID", "Name"}))

And it works. Please, notice how I had to reference the previous step twice (Source).
- The first time as it normally requires;
- The second time to feed it with the same data-set for "Table.ColumnNames", in order to wrap everything into List.Difference...

Now, I have:

ID             Name  Column 1  Column 2  Column 3  Column 4
Q25019    XXX      46             109            11             3
Q25024    YYY      59             333            55             92
Q25030    ZZZ      27             208            29             108
Q25032    AAA     6               62              10             8
Q25039    BBB      3               136            0               2
Q25040    CCC     2                26             6               0
Q25043    DDD    3                67             0               0
Q25055    EEE      13              71             18             2

But... when I try to sum the Columns (1, 2, 3, 4) up with this:

InsertedSum = Table.AddColumn(ReplacedNull, "Total",
each List.Sum(List.Difference(Table.ColumnNames(ReplacedNull), {"ID", "Name"})), type number)

...it does not work!
Of course, I am just asking for a simple sum, which can be achieved with:

InsertedSum = Table.AddColumn(ReplacedNull, "Total",
each List.Sum({[Column1], [Column2], [Column3], [Column4]}), type number)

but... what if I do really need to avoid hard-coding these columns?

I am also attaching a screenshot, shold that result unreadable...

[ImkeF · Accepted solution]

So far, you're only dealing with the table's column names and not with the actual values of each row/record. So you have to adjust your formula like so:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjQyNTAxVtJRcnFxAZIgVl5pTg6QMjMHEgZKsToQRaamQK6rqytCgSFIsbkhiGWhFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Name", type text}, {"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Sum(Record.FieldValues(Record.SelectFields(_, List.Difference(Table.ColumnNames(Source), {"ID", "Name"})))))
in
    #"Added Custom"

[karimm]

Hi @ImkeF ,

Thank you for your answer and continuous help to the community.

I've used the following method to sum up a list of columns whose names are stored in a variable named EstimateFieldNamesList.

Table.AddColumn..... each List.Sum(Record.FieldValues(Record.SelectFields(_, EstimateFieldNamesList))))

It is giving the required results, but it's EXTREMELY SLOW...

Any way to improve its performance?

Thanks again.

[ImkeF]

Hi @karimm ,

yes, you can speed this up by buffereing the EstimateFieldNamesList.

Easiest way is to add another step:

BufferedList = List.Buffer(EstimateFieldNamesList)

and use this in your Table.AddColumn expression like so:

Table.AddColumn..... each List.Sum(Record.FieldValues(Record.SelectFields(_, BufferedList))))

[karimm]

Thank you so much!

In the meantime, what I did is to unpivot those columns as they reflect values from a dimension, and use group by. 

Whis was blazing fast!

Thanks again!

[Anonymous]

Oh, it looks like my interface is broken: I cannot (re)format, no text tools available...

[v-shex-msft]

HI @Anonymous,

If you can please provide a pbix file for test, I think it will help for test and coding formula.(you can upload to onedrive or google drive and share link here)

Regards,

Xiaoxin Sheng

[Anonymous]

Hi Guys, Thank you for your answer. Yes! I am going to reformat it, as I really did not notice it that way. Sorry for that! Just to recap my post: After pivoting an attribute to get as many different fields as they happen to appear as values into the previous step under that attribute, I needed to dynamically replace the null values with a zero in this way, which actually work: ReplacedNull = Table.ReplaceValue(PivotedColumn, null, 0, Replacer.ReplaceValue, List.Difference(Table.ColumnNames(PivotedColumn), {"ID", "Name"})) In other words: by simply leaving as 'fixed' the non-numeric fields, like the Employee ID and Employee Name - while replacing the null values across all of the others. Now, I wanted to sum up these fields (the ones which I replaced null with zero into) in order to avoid the hard-coding of their labels, being those of a variable nature... I tried: InsertedSum = Table.AddColumn(ReplacedNull, "Total", each List.Sum(List.Difference(Table.ColumnNames(ReplacedNull), {"ID", "Name"})), type number) ...and it does not work, as it triggers this error: Expression.Error: We cannot apply operator - to types Text and Text. Details: Operator=- Left=Archive Right=Archive As an example, starting from a situation like the following (let it be our "Source"): ID Name Column 1 Column 2 Column 3 Column 4 Q25019 XXX 46 109 11 3 Q25024 YYY 59 333 55 92 Q25030 ZZZ 27 208 29 108 Q25032 AAA 6 62 10 8 Q25039 BBB 3 136 null 2 Q25040 CCC 2 26 6 null Q25043 DDD 3 67 null null Q25055 EEE 13 71 18 2 I replaced the null values with the first formula, above, in order to avoid to hard-code the field labels (they may change): ReplacedNull = Table.ReplaceValue(Source, null, 0, Replacer.ReplaceValue, List.Difference(Table.ColumnNames(Source), {"ID", "Name"})) And it works. Please, notice how I had to reference the previous step twice (Source). - The first time as it normally requires; - The second time to feed it with the same data-set for "Table.ColumnNames", in order to wrap everything into List.Difference... Now, I have: ID Name Column 1 Column 2 Column 3 Column 4 Q25019 XXX 46 109 11 3 Q25024 YYY 59 333 55 92 Q25030 ZZZ 27 208 29 108 Q25032 AAA 6 62 10 8 Q25039 BBB 3 136 0 2 Q25040 CCC 2 26 6 0 Q25043 DDD 3 67 0 0 Q25055 EEE 13 71 18 2 But... when I try to sum the Columns (1, 2, 3, 4) up with this: InsertedSum = Table.AddColumn(ReplacedNull, "Total", each List.Sum(List.Difference(Table.ColumnNames(ReplacedNull), {"ID", "Name"})), type number) ...it does not work! Of course, I am just asking for a simple sum, which can be achieved with: InsertedSum = Table.AddColumn(ReplacedNull, "Total", each List.Sum({[Column1], [Column2], [Column3], [Column4]}), type number) but... what if I do really need to avoid hard-coding these columns?

[v-shex-msft]

Hi @Anonymous,

In my option, you can write a variable step to get table fields as list.

Then use that variable in in expression column name part which you mentioned as parameter to achieve dynamic fields name.

Regards,

Xiaoxin Sheng

[ImkeF]

Please post sample data.

And next time, please reformat your post, as it is almost unreadable.