Forum Discussion

ThueStensgaard2's avatar
ThueStensgaard2
New Member
3 years ago

Excel formula into m-language

Hi forum,

 

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

 

 

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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's avatar
      ThueStensgaard2
      New Member

      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's avatar
        Anonymous
        Not applicable

        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.

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion

    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's avatar
      Anonymous
      Not applicable

      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.