Forum Discussion
Calculate time diffrence between two rows based on criterias
Helle Petter120
yes, we are almost there..... hoping that my function is working properly 🙂
your query should look something like this
let Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYRM9QyM9IwNDSwUDCysDA6VYHVyShqY4JS1hkk5ACQtcxmKXNMItaWlljCZpCpc0NABLxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Area = _t, Drift = _t, Date_Time = _t]), changedtype = Table.TransformColumnTypes(Quelle,{{"Area", type text}, {"Drift", Int64.Type}, {"Date_Time", type datetime}}), FinalTable = fnConvertTable(changedtype) in FinalTablewhere my function is the last variable that is then passed as result with the in-statement. The part above the FInalTable-variable should be your old query...
If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun
Jimmy
Hi Jimmy801,
I cant some kind of error and i think it just has something to do with the date format.
When i import the table into Power Query the date format in the column "Date_Time" is as we use it in sweden YYYY-MM-DD HH:MM:SS like this:
But when i run your code i get an error like this for the "Date_Time":
And when i check the error i get this information and it says in english "DateFormat:Error. The indata cant be parsed as given for a DateTime-Value
Information: 14.12.2019 08:00
So i guess i need to convert the DD. MM.YYY HH:MM to YYYY-MM-DD HH:MM:SS ?
Hello Petter120 ,
see the problem. Then let'ts try like this.
Create a new blank query, past this code and name the function to "fnConvertTable"
then go to your query and write this code
FinalTable = fnConvertTable(VariableOfYourLastStep)
in
FinalTable
then it should work
(tTable as table) =>
let
#"Gruppierte Zeilen" = Table.Group(tTable, {"Area", "Drift"}, {{"AllRows", each _, type table [Area=text, Drift=number, Date_Time=datetime]}}),
CreateDifferenceRowByRow = (tTable as table) => List.Generate
(
()=>
[
Duration = #duration(0,0,0,0),
Counter = 1
],
each [Counter]<= Table.RowCount(tTable),
(oldRecord)=>
[
Duration = tTable[Date_Time]{oldRecord[Counter]}-tTable[Date_Time]{oldRecord[Counter]-1},
Counter = oldRecord[Counter]+1
],
each [Duration]
),
AddDuration = Table.AddColumn
(
#"Gruppierte Zeilen",
"Duration",
each CreateDifferenceRowByRow([AllRows])
),
AddCombine = Table.AddColumn
(
AddDuration,
"CombineAllRowsWIthDuration",
each Table.FromColumns
(
Table.ToColumns
(
[AllRows]
)&{[Duration]},
Table.ColumnNames
(
[AllRows]
)&{"Duration"}
)
),
DeleteNotNeededColumns = Table.RemoveColumns
(
AddCombine,
{"AllRows", "Duration"}
),
Expand = Table.ExpandTableColumn
(
DeleteNotNeededColumns,
"CombineAllRowsWIthDuration",
{"Date_Time", "Duration"}, {"Date_Time", "Duration"}
),
AdaptType = Table.TransformColumnTypes
(
Expand,
{{"Date_Time", type datetime}, {"Duration", type duration}}
)
in
AdaptType
If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun
Jimmy
Ok Jimmy801 i thinking im almost there, just clarify a few things:
After the import of "Tabell1" i go and create a blank query and add the code.
Then it ask me to add a parameter where i chose "Tabell1"
Then i rename the function to "fnConvertTable"
Then "Tabell1" looks like this
When i open the advanced Editor it looks like this:
Then i pass the new code
And i its here im guessing om doing something wrong?Because i get this error: Expression.SyntaxError. Token Eof expected
Helle Petter120
yes, we are almost there..... hoping that my function is working properly 🙂
your query should look something like this
let Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYRM9QyM9IwNDSwUDCysDA6VYHVyShqY4JS1hkk5ACQtcxmKXNMItaWlljCZpCpc0NABLxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Area = _t, Drift = _t, Date_Time = _t]), changedtype = Table.TransformColumnTypes(Quelle,{{"Area", type text}, {"Drift", Int64.Type}, {"Date_Time", type datetime}}), FinalTable = fnConvertTable(changedtype) in FinalTablewhere my function is the last variable that is then passed as result with the in-statement. The part above the FInalTable-variable should be your old query...
If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun
Jimmy