Forum Discussion
datanalysis
2 years agoNew Member
Create a new column with calculation using conditions
Hi I have input data that looks like this, and I'd like to create a view with a new column "Month on Month Change", calculating difference on col "Actual" between current month and last month for ite...
- 2 years ago
s = your_table, f = (tbl) => [rows = List.Buffer(Table.ToRecords(Table.Sort(tbl, "Month"))), gen = List.Generate( () => [i = 0, r = rows{0} & [Month on month change = 0]], (x) => x[i] < List.Count(rows), (x) => [i = x[i] + 1, r = rows{i} & [Month on month change = rows{i}[Actual] - x[r][Actual]]], (x) => x[r] )][gen], group = Table.Group(s, "Segment", {"x", f}), z = Table.FromRecords(List.Combine(group[x])) - 2 years ago
Hi datanalysis, different approach here.
Result
let fnShift = (tbl as table, col as text, shift as nullable number, optional newColName as text, optional _type as type) as table => //v 3. parametri zadaj zaporne cislo ak chces posunut riadky hore, kladne ak dole, 4. je nepovinny (novy nazov stlpca), 5. je nepovinny typ let a = Table.Column(tbl, col), b = if shift = 0 or shift = null then a else if shift > 0 then List.Repeat({null}, shift) & List.RemoveLastN(a, shift) else List.RemoveFirstN(a, shift * -1) & List.Repeat({null}, shift * -1), c = Table.FromColumns(Table.ToColumns(tbl) & {b}, Table.ColumnNames(tbl) & ( if newColName <> null then {newColName} else if shift = 0 then {col & "_Duplicate"} else if shift > 0 then {col & "_PrevValue"} else {col & "_NextValue"} )), d = Table.TransformColumnTypes(c, {List.Last(Table.ColumnNames(c)), if _type <> null then _type else type any}) in d, Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCilKzCsuyC8qSSzJzM9T0lFyT81LLUrMAbIMtQ0NgZSRgZExiGdgBCQtLYEEiOuSWJaZohSrg9cEI21DA4QJYL2GBiARXUNijTDWtkQywRBGGBCr30TbAtkPBnB3oBjgmJycn5ubn0IoEMzMgIQ52AcmQMIrPyOPgH7UIDAzBxEWUPuJ0I7ifYhuc6j3idCN4nmIbjMUu2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Segment = _t, Section = _t, Month = _t, Year = _t, Expected = _t, Actual = _t, #"Expected VS Actual" = _t, Owner = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Expected", type number}, {"Actual", type number}, {"Expected VS Actual", type number}}), GroupedRows = Table.Group(ChangedType, {"Segment"}, {{"All", each [ a = fnShift(_,"Actual",1, null, type number), b = Table.AddColumn(a, "Month on month change", (x)=> x[Actual] - x[Actual_PrevValue], type number), c = Table.RemoveColumns(b, {"Actual_PrevValue"}) ][c], type table}}), CombinedAll = Table.Combine(GroupedRows[All]) in CombinedAll
dufoq3
2 years agoCommunity Champion
Hi datanalysis, different approach here.
Result
let
fnShift = (tbl as table, col as text, shift as nullable number, optional newColName as text, optional _type as type) as table =>
//v 3. parametri zadaj zaporne cislo ak chces posunut riadky hore, kladne ak dole, 4. je nepovinny (novy nazov stlpca), 5. je nepovinny typ
let
a = Table.Column(tbl, col),
b = if shift = 0 or shift = null then a else if shift > 0
then List.Repeat({null}, shift) & List.RemoveLastN(a, shift)
else List.RemoveFirstN(a, shift * -1) & List.Repeat({null}, shift * -1),
c = Table.FromColumns(Table.ToColumns(tbl) & {b}, Table.ColumnNames(tbl) &
( if newColName <> null then {newColName} else
if shift = 0 then {col & "_Duplicate"} else
if shift > 0 then {col & "_PrevValue"}
else {col & "_NextValue"} )),
d = Table.TransformColumnTypes(c, {List.Last(Table.ColumnNames(c)), if _type <> null then _type else type any})
in
d,
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCilKzCsuyC8qSSzJzM9T0lFyT81LLUrMAbIMtQ0NgZSRgZExiGdgBCQtLYEEiOuSWJaZohSrg9cEI21DA4QJYL2GBiARXUNijTDWtkQywRBGGBCr30TbAtkPBnB3oBjgmJycn5ubn0IoEMzMgIQ52AcmQMIrPyOPgH7UIDAzBxEWUPuJ0I7ifYhuc6j3idCN4nmIbjMUu2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Segment = _t, Section = _t, Month = _t, Year = _t, Expected = _t, Actual = _t, #"Expected VS Actual" = _t, Owner = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Expected", type number}, {"Actual", type number}, {"Expected VS Actual", type number}}),
GroupedRows = Table.Group(ChangedType, {"Segment"}, {{"All", each
[ a = fnShift(_,"Actual",1, null, type number),
b = Table.AddColumn(a, "Month on month change", (x)=> x[Actual] - x[Actual_PrevValue], type number),
c = Table.RemoveColumns(b, {"Actual_PrevValue"})
][c], type table}}),
CombinedAll = Table.Combine(GroupedRows[All])
in
CombinedAll