Forum Discussion
Loading data too large
Cases where the worker may not have registered or there may have been a failure in the entry or exit registration will be validated, this logic and the calculation I already have. Remember that the problem I was facing was the slowness and the large number of bytes that were being loaded when loading the data because of the use of readings in subsequent rows in the same query.
I've already managed to resolve the situation. What I did was duplicate the query, then I added a column with a 1-based index to the first query and a 0-based index to the second query and I merged the queries based on the indexes, taking only the "Movement Time" column and that's it.
I changed it to use the conditional but I got the same result. I think the try handles the error when it reaches the last line. I don't know in terms of performance which would be the best way.
I really think I need to try with DAX.
If you have any other solution proposals I would be very grateful.
Thanks!!!
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
I basically need to calculate the time an employee spends on the platform each time he/she goes to the platform. The data source will be a folder where files with daily access control records will be stored daily. It is important to consider that the employee may go to the platform one day and return only the next day.
Here are examples of daily records:
Gangway 19.03.25.csv
Full Name Employer Occupation Shift Movement Time From Location To Location Type NAME_1 COMPANY_1 COORDENADOR DAY 19/03/2025 16:47 FLOTEL PLATFORM A NAME_1 COMPANY_1 COORDENADOR DAY 19/03/2025 18:14 PLATFORM FLOTEL A NAME_2 COMPANY_1 CALDEIREIRO DAY 19/03/2025 01:26 FLOTEL PLATFORM A NAME_2 COMPANY_1 CALDEIREIRO DAY 19/03/2025 06:48 PLATFORM FLOTEL A NAME_2 COMPANY_1 CALDEIREIRO DAY 19/03/2025 18:56 FLOTEL PLATFORM A NAME_2 COMPANY_1 CALDEIREIRO DAY 19/03/2025 23:48 PLATFORM FLOTEL A NAME_3 COMPANY_1 PINTOR INDUSTRIAL DAY 19/03/2025 05:55 FLOTEL PLATFORM A NAME_3 COMPANY_1 PINTOR INDUSTRIAL DAY 19/03/2025 11:25 PLATFORM FLOTEL A NAME_3 COMPANY_1 PINTOR INDUSTRIAL DAY 19/03/2025 13:09 FLOTEL PLATFORM A NAME_3 COMPANY_1 PINTOR INDUSTRIAL DAY 19/03/2025 17:49 PLATFORM FLOTEL A NAME_4 COMPANY_1 PINTOR ESCALADOR DAY 19/03/2025 05:50 FLOTEL PLATFORM A NAME_4 COMPANY_1 PINTOR ESCALADOR DAY 19/03/2025 11:26 PLATFORM FLOTEL A NAME_4 COMPANY_1 PINTOR ESCALADOR DAY 19/03/2025 13:05 FLOTEL PLATFORM A NAME_4 COMPANY_1 PINTOR ESCALADOR DAY 19/03/2025 17:51 PLATFORM FLOTEL A Gangway 20.03.25.csv
Full Name Employer Occupation Shift Movement Time From Location To Location Type NAME_1 COMPANY_1 COORDENADOR DAY 20/03/2025 06:55 FLOTEL PLATFORM A NAME_1 COMPANY_1 COORDENADOR DAY 20/03/2025 07:24 PLATFORM FLOTEL A NAME_2 COMPANY_1 CALDEIREIRO DAY 20/03/2025 01:26 FLOTEL PLATFORM A NAME_2 COMPANY_1 CALDEIREIRO DAY 20/03/2025 06:50 PLATFORM FLOTEL A NAME_2 COMPANY_1 CALDEIREIRO DAY 20/03/2025 19:01 FLOTEL PLATFORM A NAME_2 COMPANY_1 CALDEIREIRO DAY 20/03/2025 23:43 PLATFORM FLOTEL A NAME_346 COMPANY_1 MONTADOR DE ANDAIME DAY 20/03/2025 15:52 FLOTEL PLATFORM A NAME_346 COMPANY_1 MONTADOR DE ANDAIME DAY 20/03/2025 17:48 PLATFORM FLOTEL A NAME_3 COMPANY_1 PINTOR INDUSTRIAL DAY 20/03/2025 05:54 FLOTEL PLATFORM A NAME_3 COMPANY_1 PINTOR INDUSTRIAL DAY 20/03/2025 11:27 PLATFORM FLOTEL A NAME_3 COMPANY_1 PINTOR INDUSTRIAL DAY 20/03/2025 13:07 FLOTEL PLATFORM A NAME_3 COMPANY_1 PINTOR INDUSTRIAL DAY 20/03/2025 17:49 PLATFORM FLOTEL A NAME_4 COMPANY_1 PINTOR ESCALADOR DAY 20/03/2025 05:54 FLOTEL PLATFORM A NAME_4 COMPANY_1 PINTOR ESCALADOR DAY 20/03/2025 11:25 PLATFORM FLOTEL A NAME_4 COMPANY_1 PINTOR ESCALADOR DAY 20/03/2025 13:03 FLOTEL PLATFORM A NAME_4 COMPANY_1 PINTOR ESCALADOR DAY 20/03/2025 17:48 PLATFORM FLOTEL A Gangway 21.03.25.csv
Full Name Employer Occupation Shift Movement Time From Location To Location Type NAME_247 COMPANY_2 SEGURANCA PATRIMONIAL DAY 21/03/2025 00:04 PLATFORM FLOTEL A NAME_131 COMPANY_8 OPERADOR DE GUINDASTE DAY 21/03/2025 00:52 FLOTEL PLATFORM A NAME_114 COMPANY_8 SUPERVISOR DE MOV. DE CARGA DAY 21/03/2025 01:05 FLOTEL PLATFORM A NAME_224 COMPANY_1 SOLDADOR ESCALADOR DAY 21/03/2025 01:05 FLOTEL PLATFORM A NAME_266 COMPANY_8 AUX. DE MOV. DE CARGA DAY 21/03/2025 01:07 FLOTEL PLATFORM A NAME_141 COMPANY_8 MESTRE DE CABOTAGEM DAY 21/03/2025 01:07 FLOTEL PLATFORM A NAME_235 COMPANY_8 AUX. DE MOV. DE CARGA DAY 21/03/2025 01:07 FLOTEL PLATFORM A NAME_327 COMPANY_1 PINTOR ESCALADOR DAY 21/03/2025 01:11 FLOTEL PLATFORM A NAME_329 COMPANY_1 ESCALADOR DAY 21/03/2025 01:12 FLOTEL PLATFORM A NAME_368 COMPANY_1 PINTOR INDUSTRIAL DAY 21/03/2025 01:13 FLOTEL PLATFORM A NAME_369 COMPANY_1 AJUDANTE DAY 21/03/2025 01:13 FLOTEL PLATFORM A NAME_90 COMPANY_1 CALDEIREIRO DAY 21/03/2025 01:14 FLOTEL PLATFORM A NAME_28 COMPANY_1 SUPERVISOR ESCALADOR DAY 21/03/2025 01:15 FLOTEL PLATFORM A NAME_97 COMPANY_1 PINTOR DAY 21/03/2025 01:15 FLOTEL PLATFORM A NAME_219 COMPANY_1 PINTOR DAY 21/03/2025 01:16 FLOTEL PLATFORM A If you need more information I will add it in the next posts.
Thank you very much for your availability.
let Source = Folder.Files("C:\FOLDER"), #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "Gangway") and Text.EndsWith([Name], ".csv")), Data = Table.AddColumn(#"Filtered Rows","Data", each Table.PromoteHeaders(Csv.Document([Content],[Delimiter="#(tab)", Columns=8, Encoding=65001, QuoteStyle=QuoteStyle.Csv]), [PromoteAllScalars=true])), #"Removed Other Columns" = Table.SelectColumns(Data,{"Name", "Data"}), #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", Table.ColumnNames(#"Removed Other Columns"{0}[Data])) in #"Expanded Data"I had already done these data loading steps, but what I need help with is to process them so that I can calculate the time each worker spent on the PLATFORM. Note that there are two columns, From Location and To Location, which indicate whether the worker is going to or returning from the platform, in addition to the Movement Time column, which indicates when the movement occurred. I need to subtract the data in the Movement Time column from the next row by the current row. To do this, I was duplicating the column and making all the values move to the previous row, and it was at this point that the data loading time started to slow down. I believe this is due to the large amount of calculations that Power Query needed to do when working with indexes.
The image above shows what I was doing.