Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jwi1
Post Patron
Post Patron

row numbering wrong/ how to calculate

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

 

Capture.PNG

1 ACCEPTED SOLUTION
ribisht17
Super User
Super User
4 REPLIES 4
Vijay_A_Verma
Super User
Super User

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
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

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

 

Untitled.png

 

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"

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


ribisht17
Super User
Super User

Hi @ribisht17 , @Vijay_A_Verma , @Jihwan_Kim 

 

problem solved, thank you!

 

John

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors