Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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))
Hi @bdpr_95 ,
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