Forum Discussion
Solved
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 items fall under the same "Segment".
Input:
| Segment | Section | Month | Year | Expected | Actual | Expected VS Actual | Owner |
| Transportation | General | 1+11 | 2023 | 102 | 99 | 3 | David |
| Transportation | General | 2+10 | 2023 | 99 | 100 | -1 | David |
| Transportation | General | 3+9 | 2023 | 91 | 91 | 0 | David |
| Transportation | General | 4+8 | 2023 | 100 | 99 | 1 | David |
| Accommodation | General | 1+11 | 2023 | 66 | 70 | -4 | John |
| Accommodation | General | 2+10 | 2023 | 67 | 68 | 1 | John |
| Accommodation | General | 3+9 | 2023 | 67 | 67 | 0 | John |
| Accommodation | General | 4+8 | 2023 | 67 | 66 | 1 | John |
i.e:
Month on Month change Row 1 = blank as there is no previous month data available for this segment
Month on Month change Row 2 = 1 (calculation being: select 'Segment - Transportation', use 'Actual 2+10' - 'Actual 1+11')
| Segment | Section | Month | Year | Expected | Actual | Expected VS Actual | Owner | Month on month change |
| Transportation | General | 1+11 | 2023 | 102 | 99 | 3 | David | |
| Transportation | General | 2+10 | 2023 | 99 | 100 | -1 | David | 1 |
Following this logic, the full Output will look like:
| Segment | Section | Month | Year | Expected | Actual | Expected VS Actual | Owner | Month on month change |
| Transportation | General | 1+11 | 2023 | 102 | 99 | 3 | David | |
| Transportation | General | 2+10 | 2023 | 99 | 100 | -1 | David | 1 |
| Transportation | General | 3+9 | 2023 | 91 | 91 | 0 | David | -9 |
| Transportation | General | 4+8 | 2023 | 100 | 99 | 1 | David | 8 |
| Accommodation | General | 1+11 | 2023 | 66 | 70 | -4 | John | |
| Accommodation | General | 2+10 | 2023 | 67 | 68 | 1 | John | -2 |
| Accommodation | General | 3+9 | 2023 | 67 | 67 | 0 | John | -1 |
| Accommodation | General | 4+8 | 2023 | 67 | 66 | 1 | John | -1 |
Is there a way to acheive this in PowerQuery?
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]))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
2 Replies
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]))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