row numbering wrong/ how to calculate

[Anonymous]

Hi all,

below is a simple table.

I want to calculate the values in the column difference.

Preferably in power query.

the difference between 2 and 1 = 1

the difference between 3 and 2 = 1

the difference between 5 and 3 = 2

etc.

How you can give me some guidance how to do that.

Thanks!

John

[Vijay_A_Verma]

Added an Index column and put following formula in a custom column

=try [number]-#"Added Index"[number]{[Index]-1} otherwise 0

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSFEyag0kLMGkJJg0NlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [number = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"number", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "difference", each try [number]-#"Added Index"[number]{[Index]-1} otherwise 0, type number),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

I tried to do this in Power Query Editor by clicking UI.

The below is the copy from the Advaned Editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSFEyag0kLMGkJJg0NlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index.1"}, #"Added Index1", {"Index"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Number"}, {"Added Index1.Number"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Added Index1",{{"Number", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", each if [Added Index1.Number] = null then 0 else 
[Number]-[Added Index1.Number]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1", "Added Index1.Number"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom", "Difference"}})
in
    #"Renamed Columns"

[ribisht17 · Accepted solution]

@Anonymous 

This has been solved here Solved: Calculate Difference Between Previous Row - Microsoft Power BI Community

Regards,

Ritesh

[Anonymous]

Hi @ribisht17 , @Vijay_A_Verma , @Jihwan_Kim 

problem solved, thank you!

John