Forum Discussion
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
- AnonymousNot 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.- ThueStensgaard2New 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?
- AnonymousNot 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_DanielCommunity 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})
- AnonymousNot 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.