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? 🤞😊    
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

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.