How to summarize columns when them could appear or not every update data

[JOSELUISMTZRMZ1]

Hello Everyone

I have a Transform where I used Unpivot option to make the values of a column be columns

then I want to add 3 new columns to summarize ABCD, EFGH, IJKL. 

This transform works in a specific time the source file be replaced to process new data every month

The problem is that a value of CVHOLD column might not appear which makes that the add column fails because one or more columns might not be created when pivot step.

I am out of ideas how to add existing columns and not to specific column names

Any suggest is appreaciated.

[KT_Bsmart2gethe]

Hi @JOSELUISMTZRMZ1 ,

I am not sure if I am following. What is the sum pattern? Sum of all the CVHOLD at row level?

Use @wdx223_Daniel 's proposed formula and it should work.

Consider the first column is [MSPN+LOC_Name]. 

Add a custom column:

List.Sum(List.RemoveFirstN(Record.ToList(_),1))

Regards

KT

Regards

Kerwin

[JOSELUISMTZRMZ1]

My first images were an easy example what I'm trying to do but after taking a look on previous steps I could get the results what I need with both your suggestions @wdx223_Daniel @KT_Bsmart2gethe .

I also want to thank @rohit_singh this article is also functional. 

each List.Sum(Record.ToList(_))  is the solution if you want to sum without to indicate columns name

Thanks!!!

[JOSELUISMTZRMZ1]

Hello,

Thanks for the suggestion, unfortunately it doesn't work as needed on my case. Anyway, I had to use the option "Group by" then using "Sum" option in order to do the "sum" without pointing the column names then following with my pivot transform.

[rohit_singh]

Hello @JOSELUISMTZRMZ1 ,

In you case where the columns are dynamic and might change occassionally, you have to ensure that all references to your column names are dynamic. This can be done by creating a list that stores a list of column names that updates dynamically everytime data is updated, and referencing this list in all downstream queries.

A similar approach has been explained in the following post (although the post is to split a column dynamically but the base logic is similar): 

https://eforexcel.com/wp/power-query-tips-tricks-6-split-a-column-dynamically-i-e-no-dependency-on-t...

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[wdx223_Daniel · Accepted solution]

each List.Sum(Record.ToList(_))