Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.