Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Add Columns in a Table without expanding it

Hello everyone,

 

I'm trying to add a new column in a table without expanding it, but with no success.
Here is a print of my actual situation:

 

 

 

What I need is to create a column in the next step, that does the following calculation: the "COVER" Column less the "CONSUMPTION" Column for the first row (index = 0).

For the others, grab the "COVER" value for the current row and do the subtraction to the result obtained in the previous row.
Anyone can help?
Find below the M Code:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyNjJR0lQxg2MACSxqYGSrE6WJQYEVYCkjTFq8IYrwojqBmGpjASqxJDmCE4VRihqIgFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, CLIENT = _t, INDEX = _t, COVER = _t, CONSUMPTION = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CLIENT", type text}, {"INDEX", Int64.Type}, {"COVER", Int64.Type}, {"CONSUMPTION", Int64.Type}, {"DATE", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"DATE", Order.Ascending},{"CLIENT", Order.Ascending},{"INDEX", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"CLIENT"}, {{"AllRows", each _, type table [DATE=nullable date, CUI=nullable text, CLICK=nullable number, COVER=nullable number, CONSUMPTION=nullable number]}})
in
    #"Grouped Rows"

 

 

I tried to do a test and I created a custom column but this was the best that I achieved:

#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Table", each Table.AddColumn([AllRows], "Table", each 5))

 

Need Help
Tips and Tricks

1 Reply

  • latimeria's avatar
    latimeria
    Solution Specialist
    3 years ago

    Hi Anonymous ,

     

    I would do the calculation during the grouping: 

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyNjJR0lQxg2MACSxqYGSrE6WJQYEVYCkjTFq8IYrwojqBmGpjASqxJDmCE4VRihqIgFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, CLIENT = _t, INDEX = _t, COVER = _t, CONSUMPTION = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CLIENT", type text}, {"INDEX", Int64.Type}, {"COVER", Int64.Type}, {"CONSUMPTION", Int64.Type}, {"DATE", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"DATE", Order.Ascending},{"CLIENT", Order.Ascending},{"INDEX", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(
        #"Sorted Rows", 
        {"CLIENT"}, 
        {
            {"AllRows", each 
                let
                    ListCover = List.Buffer([COVER]),
                    Result = 
                        List.Generate(
                            ()=> [RT = ListCover{0} - [CONSUMPTION]{0}, Counter = 0 ],
                            each [Counter] < List.Count( ListCover),
                            each [
                                RT = List.Sum( {[RT], ListCover{[Counter]+1}}),
                                Counter = [Counter] + 1
                            ],
                            each [RT]

                        )
                in  /* transform table to columns, add new column and back to table */
                    Table.FromColumns(
                        Table.ToColumns(_) & { Value.ReplaceType(Result, type {Int64.Type})},
                        Table.ColumnNames(_) & {"Result"}
                    )
            }
        }
    ),
    Expanded = Table.Combine(#"Grouped Rows"[AllRows])
in
    Expanded

     

    Here is the result:

     

    You can find explantion here Compute a Running Total by Category in Power Query - BI Gorilla

    and here Quickly Create Running Totals in Power Query • My Online Training Hub