Forum Discussion

Einomi's avatar
Einomi
Helper V
3 years ago
Solved

Sum certain columns

Hi,

 

Very common topic, but I cannot find an easy and proper solution. I have data coming and I applied some transformations to unpivot the data. As a result, I have 7 columns (the 7 first) that give me details on the order. and all the other columns (up to 90 columns) are the product they ordered (in the headers the name of the product, in the cell the quantity)

 

I would like to add a column that gives me the total quantity of the products ordered. Obviously dynamically. I found some codes online. This one work but it does not add a column with the quantity rather a table with all my data unpivoted and a sum column...

 

let
    Source = Consolidation,
    #"Colonnes supprimées" = Table.RemoveColumns(Source,{"What is your e-mail address ?", "Loyalty Card", "Card Number"}),
    #"Espaces supprimés" = Table.TransformColumns(#"Colonnes supprimées",{{"City", Text.Trim, type text}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Espaces supprimés", "Personnalisé", each 
    let  
        FirstColRef = 7,
        NumColsToSum = Table.ColumnCount(#"Colonnes supprimées")-FirstColRef,
        RowTotal = Table.AddColumn(#"Colonnes supprimées", "Sum", each List.Sum(List.Range(Record.ToList(_),FirstColRef,NumColsToSum)))
    in    
        RowTotal
        
        )
in
    #"Personnalisée ajoutée"

 

Can someone help please ?

  • This would work

    let
        Source = Consolidation,
        #"Colonnes supprimées" = Table.RemoveColumns(Source,{"What is your e-mail address ?", "Loyalty Card", "Card Number"}),
        #"Espaces supprimés" = Table.TransformColumns(#"Colonnes supprimées",{{"City", Text.Trim, type text}}),
        FirstColRef = 7,
        RowTotal = Table.AddColumn(#"Colonnes supprimées", "Sum", each List.Sum(List.Skip(Record.ToList(_),FirstColRef)))
        in    
            RowTotal

2 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional

    This would work

    let
        Source = Consolidation,
        #"Colonnes supprimées" = Table.RemoveColumns(Source,{"What is your e-mail address ?", "Loyalty Card", "Card Number"}),
        #"Espaces supprimés" = Table.TransformColumns(#"Colonnes supprimées",{{"City", Text.Trim, type text}}),
        FirstColRef = 7,
        RowTotal = Table.AddColumn(#"Colonnes supprimées", "Sum", each List.Sum(List.Skip(Record.ToList(_),FirstColRef)))
        in    
            RowTotal