Forum Discussion
How do I subtract a value based upon the difference in date?
Hi All,
I hope you're well,
I wondering if someone might be able to advise me regarding how I can subtract these pseudonymised(fruit values) per day by the previous day to see the increase on a whole or average level.
This is my table with the columns
This is my current Query I would like to create a new query one which subtract for example apples for 05.11.2022 - 04.11.2022 so I would like to calculate the difference of increase of decrease in number of fruits per day.
Many thanks
Hi Anonymous ,
You can sort your table on [FruitName] and [Date], then merge the table on itself using two indexes.
A full explanation and example query here:
Pete
Hi Anonymous
I create a sample by following Pete's solution. Hope this helps.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9KzEtPLVbSUTI00gdCAyMjINvC0lgpVidaKSAzLzWxoCAHQ4GpuRlYAZJ2Q30TuKyFARbtSAosTUwxtZsitGOzHUmBOdR8FO1ABJO2NMKqH6HCAmRALAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fruit = _t, Date = _t, #"Count total per day" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fruit", type text}, {"Date", type date}, {"Count total per day", Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Fruit", Order.Ascending}, {"Date", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type), #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type), #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Fruit", "Index"}, #"Added Index1", {"Fruit", "Index.1"}, "Added Index1", JoinKind.LeftOuter), #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Count total per day"}, {"Count total per day.1"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded Added Index1",{{"Count total per day.1", "Previous Count total per day"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Difference", each [Count total per day] - [Previous Count total per day]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1", "Previous Count total per day"}) in #"Removed Columns"Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
4 Replies
Hi Anonymous ,
You can sort your table on [FruitName] and [Date], then merge the table on itself using two indexes.
A full explanation and example query here:
Pete
Hi BA_Pete,
I apologise for my very late reply. I really appreciate the answer you have posted. However, I am looking to run queries without applying surface level filters and trying to apply simpler queries as shown in my screenshot
Many thanks,
Nida
Hi Anonymous
I create a sample by following Pete's solution. Hope this helps.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9KzEtPLVbSUTI00gdCAyMjINvC0lgpVidaKSAzLzWxoCAHQ4GpuRlYAZJ2Q30TuKyFARbtSAosTUwxtZsitGOzHUmBOdR8FO1ABJO2NMKqH6HCAmRALAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fruit = _t, Date = _t, #"Count total per day" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fruit", type text}, {"Date", type date}, {"Count total per day", Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Fruit", Order.Ascending}, {"Date", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type), #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type), #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Fruit", "Index"}, #"Added Index1", {"Fruit", "Index.1"}, "Added Index1", JoinKind.LeftOuter), #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Count total per day"}, {"Count total per day.1"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded Added Index1",{{"Count total per day.1", "Previous Count total per day"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Difference", each [Count total per day] - [Previous Count total per day]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1", "Previous Count total per day"}) in #"Removed Columns"Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.NewStep=let a=Table.Sort(#"Filtered Rows1","Dates") in Table.FromRecords(List.Accumulate(Table.ToRecords(a),{{},[]},(x,y)=>let b=Record.FieldOrDefault(x{1},y[name],0) in {x{0}&{y&[Count=y[#"Count Total per day"]-b]},x{1}&Record.AddField([],y[name],y[#"Count Total per day"])}){0})
