Calculate Difference of a balance for users; between dates.

[Anonymous]

Hi all! 

Please see below picture. So I guess my problem is kinda straight forward but can't find the answer.
I might be searching for the wrong terms. Apologies. 

Anyhow. As you can see below the first column "link" is a unique "User" (lets say) and the User has a balance in the last column "Claimed Essence". This balance increases for everyday as can see with the "DATE" and "Claimed Essence" Value. 
I have managed to derive a calculated column for the "previous date" for the balance value (not sure if I need it). 

What I want to do is to calculate the DIFFERENCE (The increased value) between the balance at the previous date for this "LINK" vs the DATE to get the (new) "INCREASED VALUE" that is the "added" value to the balance. 
What would the formula be for that calculated column "INCREASED VALUE"?

Exmaple outcome: 
Row 2 (from top) would say: 2    (844-842 = 2)
Row 3 (from top) would say: 8 (852-844 =8 )

All fields are in the same data table just like visualized but there are many different unique "links".
Each Unique "link" only appears once on every date with its own balance.  Hence granularity of the table is per "link" per "date".
See it like "accounts" at a bank with increased "balance" for each day and the question is to calc the added value with a calculated column. 

Thanks a lot guys!

[serpiva64 · Accepted solution]

Hi, 

you can obtain this

by applying this steps

let
Source = Excel.Workbook(File.Contents(___________), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Added Index" = Table.AddIndexColumn(Table1_Table, "GenIndex", 1, 1, Int64.Type),
#"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Date", type date}, {"Tier", Int64.Type}, {" Class", Int64.Type}, {" Tiles", Int64.Type}, {" Claimed Essence", Int64.Type}, {" Promised Essence", Int64.Type}, {" Has Holo", type any}, {" Country", type text}, {" Location", type text}, {" Description", type text}, {" For Sale", Int64.Type}, {"Link", type text}, {"EDC", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {" Country", "Link"}, {{"Count", each _, type table [Date=nullable date, Tier=nullable number, #" Class"=nullable number, #" Tiles"=nullable number, #" Claimed Essence"=nullable number, #" Promised Essence"=nullable number, #" Has Holo"=text, #" Country"=nullable text, #" Location"=nullable text, #" Description"=nullable text, #" For Sale"=nullable number, Link=nullable text, EDC=nullable text, GenIndex=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.AddIndexColumn([Custom],"Index1",1)),
#"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {" Claimed Essence", " Class", " Description", " For Sale", " Has Holo", " Location", " Promised Essence", " Tiles", "Date", "EDC", "GenIndex", "Index", "Index1", "Tier"}, {" Claimed Essence", " Class", " Description", " For Sale", " Has Holo", " Location", " Promised Essence", " Tiles", "Date", "EDC", "GenIndex", "Index", "Index1", "Tier"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.1",{"Count", "Custom"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Link", "Index"}, #"Removed Columns", {"Link", "Index1"}, "Removed Columns", JoinKind.LeftOuter),
#"Expanded Removed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Removed Columns", {" Claimed Essence"}, {" Claimed Essence.1"}),
#"Sorted Rows" = Table.Sort(#"Expanded Removed Columns",{{"GenIndex", Order.Ascending}}),
#"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "Difference", each if [#" Claimed Essence.1"] = null then 0 else [#" Claimed Essence"]-[#" Claimed Essence.1"]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Index", "Index1", " Claimed Essence.1"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{" Claimed Essence", Int64.Type}, {"Difference", Int64.Type}, {" Promised Essence", Int64.Type}})
in
#"Changed Type1"

If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !

[serpiva64]

Hi,

if this is what you need

 you can:

- insert 2 column index (one starting from 0 and one from 1)

- merge query

- expand

- sort rows on Index ascending (starting from 0)

- and finally add column with difference

If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !

[Anonymous]

Hi !

Thank you for giving a go to support me! 😃  I did not manage to reach the right answer. See picture below. 
I think it is because it needs to consider that I have many several unique "links" not only one. 

I have uploaded my rawdata table here that would perhaps clarify what the data looks like: 
https://easyupload.io/oabeye

[serpiva64 · Accepted solution]

Hi, 

you can obtain this

by applying this steps

let
Source = Excel.Workbook(File.Contents(___________), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Added Index" = Table.AddIndexColumn(Table1_Table, "GenIndex", 1, 1, Int64.Type),
#"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Date", type date}, {"Tier", Int64.Type}, {" Class", Int64.Type}, {" Tiles", Int64.Type}, {" Claimed Essence", Int64.Type}, {" Promised Essence", Int64.Type}, {" Has Holo", type any}, {" Country", type text}, {" Location", type text}, {" Description", type text}, {" For Sale", Int64.Type}, {"Link", type text}, {"EDC", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {" Country", "Link"}, {{"Count", each _, type table [Date=nullable date, Tier=nullable number, #" Class"=nullable number, #" Tiles"=nullable number, #" Claimed Essence"=nullable number, #" Promised Essence"=nullable number, #" Has Holo"=text, #" Country"=nullable text, #" Location"=nullable text, #" Description"=nullable text, #" For Sale"=nullable number, Link=nullable text, EDC=nullable text, GenIndex=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.AddIndexColumn([Custom],"Index1",1)),
#"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {" Claimed Essence", " Class", " Description", " For Sale", " Has Holo", " Location", " Promised Essence", " Tiles", "Date", "EDC", "GenIndex", "Index", "Index1", "Tier"}, {" Claimed Essence", " Class", " Description", " For Sale", " Has Holo", " Location", " Promised Essence", " Tiles", "Date", "EDC", "GenIndex", "Index", "Index1", "Tier"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.1",{"Count", "Custom"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Link", "Index"}, #"Removed Columns", {"Link", "Index1"}, "Removed Columns", JoinKind.LeftOuter),
#"Expanded Removed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Removed Columns", {" Claimed Essence"}, {" Claimed Essence.1"}),
#"Sorted Rows" = Table.Sort(#"Expanded Removed Columns",{{"GenIndex", Order.Ascending}}),
#"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "Difference", each if [#" Claimed Essence.1"] = null then 0 else [#" Claimed Essence"]-[#" Claimed Essence.1"]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Index", "Index1", " Claimed Essence.1"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{" Claimed Essence", Int64.Type}, {"Difference", Int64.Type}, {" Promised Essence", Int64.Type}})
in
#"Changed Type1"

If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !

[Anonymous]

Very exciting but it seems like your message got some issue displaying the steps ? 😃 I only see a lot of code ? 😃

[serpiva64]

hi, 

i hoped you were able to get the M code and to replicate it.

- i added for first a column GenIndex to grab the original order 

- then i grouped by

- added a fist custom column with an index starting from 0

- and a second starting from 1

- then expanded

- removed column Count and Custom

- merged query on itself

- expanded

- sorted ascending per GenIndex

- created the difference column

- then removed useless column and changed type for column i need to sum

that's done

If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !

[Anonymous]

This is amazing. Tried to follow each step but Must have done something wrong at one of the steps. Just to check the code you sent is that possible to just copy and paste into my power query somewhere to make it execute all the steps ? (Instead of me doing the steps manually?) 

And / or: is there a way for me to copy my steps for you to easier see where I made something incorrect ? 

You are terrific!

[serpiva64 · Accepted solution]

This are the steps i've applied

You can compare your code in Advanced editor with the one i sent before.

If you receive an error send it to me 

[Anonymous]

I got it to work by copying your code into my Advanced editor. Not sure what I missed but will check later! This is awesome! Thank you very much !!