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
alvaroiark
New Member

Calculate column with digited value

Hi All,


I need to do the same calculation as in the image below, with custom column and m language, however the first value is manually inputed, that is, it is a fixed value, the sequence takes the prior value and adds and decreases the cells.

 

screen_capture.png

 

Best Regards,

Alvaro

1 ACCEPTED SOLUTION
collinsg
Super User
Super User

Good day Alvaro,

This problem is a variation on the theme of calculating a running total. Nick de Groot has a comprehensive article on running totals here. My solution uses a simple but slow approach to running total but if performance is a concern you could use one of the faster methods described by Nick.

 

My data looked like this – the balance column contained nulls except for the first row.

collinsg_0-1685516563186.png

 

My steps were,

  1. Replace the nulls in the Balance column with zero.
  2. Add an index column.
  3. Add a column called “i1+i2-o1-o2” which calculates Input1 + Input2 – Output1 – Output2.
  4. Add a column called “Running Total” using the List.Sum and List.FirstN functions.
  5. Remove unneeded column.
  6. Rename “Running Total” as “Balance”.

There will be more elegant ways to do this without adding then removing columns but this step by step approach makes the route to the solution simple to follow.

 

This is my output

collinsg_2-1685516755709.png

 

My M code is here.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Input1", Int64.Type}, {"Input2", Int64.Type}, {"Output1", Int64.Type}, {"Output2", Int64.Type}, {"Balance", Int64.Type}}),

    #"Replaced nulls in Balance" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Balance"}),
    #"Add Index" = Table.AddIndexColumn(#"Replaced nulls in Balance", "Index", 1, 1, Int64.Type),
    #"Added i1+i2-o1-o2" = Table.AddColumn(#"Add Index", "i1+i2-o1-o2", each [Balance] + [Input1] + [Input2] - [Output1] - [Output2], Int64.Type),
    #"Add running total" = Table.AddColumn(
#"Added i1+i2-o1-o2",
"Running Total",
each List.Sum( List.FirstN( #"Added i1+i2-o1-o2"[#"i1+i2-o1-o2"], [Index] ) ),
Int64.Type ),
    #"Remove other columns" = Table.SelectColumns(#"Add running total",{"Date", "Input1", "Input2", "Output1", "Output2", "Running Total"}),
    #"Renamed Columns" = Table.RenameColumns(#"Remove other columns",{{"Running Total", "Balance"}})
in
    #"Renamed Columns"

Hope this helps

View solution in original post

2 REPLIES 2
alvaroiark
New Member

Hi @collinsg ,

 

It worked perfectly!

Thx so much!

 

Best Regards,

Alvaro

collinsg
Super User
Super User

Good day Alvaro,

This problem is a variation on the theme of calculating a running total. Nick de Groot has a comprehensive article on running totals here. My solution uses a simple but slow approach to running total but if performance is a concern you could use one of the faster methods described by Nick.

 

My data looked like this – the balance column contained nulls except for the first row.

collinsg_0-1685516563186.png

 

My steps were,

  1. Replace the nulls in the Balance column with zero.
  2. Add an index column.
  3. Add a column called “i1+i2-o1-o2” which calculates Input1 + Input2 – Output1 – Output2.
  4. Add a column called “Running Total” using the List.Sum and List.FirstN functions.
  5. Remove unneeded column.
  6. Rename “Running Total” as “Balance”.

There will be more elegant ways to do this without adding then removing columns but this step by step approach makes the route to the solution simple to follow.

 

This is my output

collinsg_2-1685516755709.png

 

My M code is here.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Input1", Int64.Type}, {"Input2", Int64.Type}, {"Output1", Int64.Type}, {"Output2", Int64.Type}, {"Balance", Int64.Type}}),

    #"Replaced nulls in Balance" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Balance"}),
    #"Add Index" = Table.AddIndexColumn(#"Replaced nulls in Balance", "Index", 1, 1, Int64.Type),
    #"Added i1+i2-o1-o2" = Table.AddColumn(#"Add Index", "i1+i2-o1-o2", each [Balance] + [Input1] + [Input2] - [Output1] - [Output2], Int64.Type),
    #"Add running total" = Table.AddColumn(
#"Added i1+i2-o1-o2",
"Running Total",
each List.Sum( List.FirstN( #"Added i1+i2-o1-o2"[#"i1+i2-o1-o2"], [Index] ) ),
Int64.Type ),
    #"Remove other columns" = Table.SelectColumns(#"Add running total",{"Date", "Input1", "Input2", "Output1", "Output2", "Running Total"}),
    #"Renamed Columns" = Table.RenameColumns(#"Remove other columns",{{"Running Total", "Balance"}})
in
    #"Renamed Columns"

Hope this helps

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors