Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
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.
If you want larger audiences you should upload a table and explain the logic to apply
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"
Thank you so much for your solution, I think it's just about working properly. I'm running into instances in Power BI when using the code where some of the Exit scans are actually earlier than the Entry scans.
My code is very similar to what you've provided, though, some of the column names are altered slightly. Do you know if your code accounts for the correct time stamp order?
Here are the lines of code I've added:
#"Grouped Rows" = Table.Group(#"Removed Columns1", {"Name", "Company", "SBIID1", "Trade", "Transit Direction"}, {{"TimeOnOff", each [FieldDateTime]}}),
#"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Name", "Company", "SBIID1", "Trade"}, {{"EE", each Table.FromColumns([TimeOnOff],[Transit Direction])}}),
#"Expanded Entry Exit Table" = Table.ExpandTableColumn(#"Grouped Rows1", "EE", {"Entry", "Exit"}, {"Entry", "Exit"})
in
#"Expanded Entry Exit Table"
I'm not sure what you mean and what problems you find. My solution gives this result. So it doesn't depend on the order of the timestamps
I'm so sorry for being unclear. My apologies.
If you look at the data provided in that Google Drive link, you'll see a record on row 11 for Person B at 17:01 (5:01 PM).
In your screenshot above, it shows that record in row 5 to the right of 06:38, which is the way it should be. (The exit stamp should come after the entry stamp)
When I used your query with the actual data, it does not always function this way.
For example, in the source file from Google Drive, If I change that time from 17:01 to 5:01, (PM to AM), the record remains on that row beside 6:38, even though it's an earlier time. It should be on it's own row, above, with a null for an entry scan.
Please, again forgive me if this remains unclear. I'll do my best to explain further.
I've created an updated version of the file, which shows how it should function. In the new file, I've changed the 17:01 record to 5:01 AM
https://drive.google.com/file/d/1__LLJIFMRe2-U-bBPjt3cgn85nBz7TKa/view?usp=sharing
Ok I think I understand what the problem is. The example you have proposed, an exit without a previous entry, can be solved. But if you have to manage general cases of this type, for example entry without exit together with exit without corresponding entry, you have to put a lot of control instructions and you necessarily lose in performance. However, just to think about it, what is the most general case of unmatched entries and exits that can happen?