Forum Discussion
Recreating DAX Custom Column in Power Query -Calculating difference in time, based on grouped values
11 Replies
Thank you for the suggestion. Here is a link to the first time I sought help on the issue.
Check if this implement correctly the logic you want and if perform better than script DAX.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndI9D4IwEIDhv0I6k3B3/ZLbFNndCYODg4NgkEH+vTVhQMI1lomG5MkLvWsadbkNr77LjipXVf94Xrspw3AGXwAWBISZZ6Lwpu7GYQpP1eb/qJLRf9X7PqYgrdNTiEwuuRWUdTtihhHTY4bJ7Ig5BuEWT7JyrA9CK6LQMwg/FlEEs9qIVQtG650CSFfhOqzd/sTzQun1dYA054gKLdTbrVpW5byJG62oMpScQpJXKsY0a2GlYsqwkQYWY5b1z8DaDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Company = _t, #"Field Date Time" = _t, Direction = _t, #"Time on Site" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Company", type text}, {"Field Date Time", type datetime}, {"Direction", type text}, {"Time on Site", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Time on Site"}), #"Raggruppate righe" = Table.Group(#"Removed Columns", {"Name", "Company","Direction"}, {{"TimeOnOff", each [Field Date Time]}}), #"Raggruppate righe1" = Table.Group(#"Raggruppate righe", {"Name", "Company"}, {{"EE", each Table.FromColumns([TimeOnOff],[Direction])}}), #"Tabella EE espansa" = Table.ExpandTableColumn(#"Raggruppate righe1", "EE", {"Entry", "Exit"}, {"Entry", "Exit"}) in #"Tabella EE espansa"
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndI9D4IwEIDhv0I6k3B3/UBuU2R3JwwODg6CQQb599aEAUmvsUw0JE9e6F3bqsttfA19dlS5qofH89rPGfozlAVgQUCYlUzk3zT9NM7+qbr8H1Uxll/1vk8pSOv0FCKTS255Zd2OmGHE9JhhMjtijkG4xZOsHOuD0IooyyD8VwQRLCrQqleMtisFkK78bVgb/sTzSuntbYA05ojyLdThViOralnEQCuqDCWnkOSNijHNWtiomDJspIHFmGX9M7DuAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Company = _t, Time = _t, Direction = _t, #"Time on Site" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Company", type text}, {"Time", type datetime}, {"Direction", type text}, {"Time on Site", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Time on Site"}), chk = (tab)=> if Table.First(tab)[Direction]="Exit" then Table.InsertRows(tab,0,List.Repeat({tab{0}&[Time=null]},2)) else tab, #"Raggruppate righe" = Table.Group(#"Removed Columns", {"Name", "Company"}, {{"TimeOnOff", each chk(Table.Sort(_,"Time"))}}), #"Removed Columns1" = Table.ExpandTableColumn(#"Raggruppate righe", "TimeOnOff", {"Time", "Direction"}, {"Time", "Direction"}), #"Raggruppate righe1" = Table.Group(#"Removed Columns1", {"Name", "Company","Direction"}, {{"TimeOnOff", each [Time]}}), #"Raggruppate righe2" = Table.Group(#"Raggruppate righe1", {"Name", "Company"}, {{"EE", each Table.FromColumns([TimeOnOff],[Direction])}}), #"Tabella EE espansa" = Table.ExpandTableColumn(#"Raggruppate righe2", "EE", {"Entry", "Exit"}, {"Entry", "Exit"}) in #"Tabella EE espansa"Thank you for your continued help with this. Sorry for the delay over the weekend. I should add that it doesn't necessarily need to perform faster than DAX, it just needs to perform without causing Memory Allocation errors in PBI online.
When I tried your new code (thank you) and changed the 17:01 (5:01 PM) record to 5:01 AM, it caused three rows to have an Exit scan of 5:01 AM. There should only be one Exit scan of 5:01 AM, and the entry scan should be the next earliest time stamp, or null if there is none.
I've attached a new workbook with your new code. The code you provided is the Table in the middle on the right side. I've only changed the column header from Time to Field Date Time.
https://drive.google.com/file/d/1lsDxPIf0GRMqPATeiYb2_Tw7kj5zswGL/view?usp=sharing
Thank you again for your help through this.
As I'm still receiving memory issues using this code in a calculated column, is there any way that this could be recreated as a measure to alleviate the memory issues?
