Excel formula into m-language

[ThueStensgaard2]

Hi forum,

I am new to m-language and hope that someone can help me convert this excel formula into m-code? 🤞😊

[wdx223_Daniel]

NewStep=Table.FromRecords(List.Accumulate(Table.ToRecords(PreviousStepName),{{},[]},(x,y)=>{x{0}&{y&[Sum tid=if y[CreateAt]=x{1}[CreateAt]? then y[Tid]-x[Tid]? else null]},y}){0})

[Anonymous]

This method seems a better one.
But bro, getting an error in this method. 
Can you help?

I hope for a reply to resolve and learn the same.
Thank you.

[Anonymous]

Create a custom column and write

= if [Created At] <> [Tid] then "" else [Tid]-[Created At]

Thank you.
If this answer helps you, please mark it as solution. 

[ThueStensgaard2]

Hi VishalJhaveri,

Thank you for your feedback but this formular will not work. 

I want to compare row 1 with row 2 within a column [Created At] and if these values are equal, it should substract row 2 from row 1 in column [Tid] and return the result in the new custom column Any idea on how to solve this specific example?

[Anonymous]

Sorry for the wrong answer.

Can you please try this query below. It seems all correct to me as per my understanding and your requirement.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fcvRCQAgCADRVcLvhFMJylXC/deIFvDv4HH3Cq6YOh4DEmQKOzlpR2o2Hrv31f1GxurcflY9", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Created At" = _t, Tid = _t]),
    SortedRows = Table.TransformColumnTypes(Source,{{"Created At", type datetime}, {"Tid", type time}}),
    #"Added Custom" = Table.AddColumn(SortedRows, "Custom", each {null} & List.RemoveLastN( Table.Column( SortedRows, "Created At" ), 1 )),
    Custom = #"Added Custom"[Custom],
    Custom1 = Custom{0},
    ListOfLists = Table.ToColumns( SortedRows ) & { Custom1 },
    Custom2 = Table.FromColumns( 
     ListOfLists, 
     Table.ColumnNames( SortedRows ) & { "Previous Row Value" } 
            ),
    #"Changed Type1" = Table.TransformColumnTypes(Custom2,{{"Previous Row Value", type datetime}}),

    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Custom2", each {null} & List.RemoveLastN( Table.Column( #"Changed Type1", "Tid" ), 1 )),
    Custom3 = #"Added Custom2"[Custom2],
    Custom4 = Custom3{0},
    ListOfLists2 = Table.ToColumns( #"Changed Type1" ) & { Custom4 },
    Custom5 = Table.FromColumns( 
     ListOfLists2, 
     Table.ColumnNames( #"Changed Type1" ) & { "Previous Row Value2" } 
            ),
    #"Changed Type" = Table.TransformColumnTypes(Custom5,{{"Previous Row Value2", type time}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom", each if [Created At] = [Previous Row Value] 
then
 [Tid]-[Previous Row Value2]
 else ""),
    #"Extracted Last Characters" = Table.TransformColumns(#"Added Custom1", {{"Custom", each Text.End(Text.From(_, "en-IN"), 8), type text}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Extracted Last Characters",{{"Custom", type time}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Custom", "Duration"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Previous Row Value", "Previous Row Value2"})
in
    #"Removed Columns"

What I did?
1. Created the required table as per your data table provided in image.

2. Created 2 new columns which consists of the Previous Row Values of the Created At Column and Tid Column respectively.

3. Difference in the Tid values based upon your requirement.

4. Removing extra columns.

Thank  you.
If my answer helps you, please mark it as solution.

[Anonymous]

Another super simple approach is to add the Index columns.

M Code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fcvRCQAgCADRVcLvhFMJylXC/deIFvDv4HH3Cq6YOh4DEmQKOzlpR2o2Hrv31f1GxurcflY9", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Created At" = _t, Tid = _t]),
    SortedRows = Table.TransformColumnTypes(Source,{{"Created At", type datetime}, {"Tid", type time}}),
    #"Added Index" = Table.AddIndexColumn(SortedRows, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Previous Value of Created At", each try #"Added Index" [Created At] {[Index] - 1} otherwise null),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Previous Value of Created At", type datetime}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Previous Value of Tid", each try #"Changed Type" [Tid] {[Index] - 1} otherwise null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Previous Value of Tid", type time}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Duration", each if [Created At] = [Previous Value of Created At]
then
 [Tid]-[Previous Value of Tid]
 else ""),
    #"Extracted Last Characters" = Table.TransformColumns(#"Added Custom2", {{"Duration", each Text.End(Text.From(_, "en-IN"), 8), type text}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Extracted Last Characters",{{"Duration", type time}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type2",{"Created At", "Tid", "Duration"})
in
    #"Removed Other Columns"

Thank  you.
If my answer helps you, please mark it as solution.