Forum Discussion
Excel formula into m-language
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?
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.