Forum Discussion
Loading data too large
- Anonymous1 year ago
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 was testing loading the data at each step and the problem starts after this second line.
What is done is to generate an index column and create a new column that receives the data from the next row in the current row based on the index.
#"Índice Adicionado" = Table.AddIndexColumn(#"Linhas Filtradas", "Índice", 1, 1, Int64.Type),
#"Tempo do Próximo Movimento" = Table.AddColumn(#"Índice Adicionado", "Next Movement Time", each try #"Índice Adicionado"[Movement Time]{[Índice]} otherwise null),
The full code is this:
let
Fonte = Folder.Files("C:\FOLDER"),
#"Arquivos Ocultos Filtrados1" = Table.SelectRows(Fonte, each [Attributes]?[Hidden]? <> true),
#"Invocar Função Personalizada1" = Table.AddColumn(#"Arquivos Ocultos Filtrados1", "Transformar Arquivo (2)", each #"Transformar Arquivo (2)"([Content])),
#"Colunas Renomeadas1" = Table.RenameColumns(#"Invocar Função Personalizada1", {"Name", "Nome da Origem"}),
#"Outras Colunas Removidas1" = Table.SelectColumns(#"Colunas Renomeadas1", {"Nome da Origem", "Transformar Arquivo (2)"}),
#"Coluna de Tabela Expandida1" = Table.ExpandTableColumn(#"Outras Colunas Removidas1", "Transformar Arquivo (2)", Table.ColumnNames(#"Transformar Arquivo (2)"(#"Arquivo de Amostra (2)"))),
#"Linhas Filtradas" = Table.SelectRows(#"Coluna de Tabela Expandida1", each ([Column1] <> "" and [Column1] <> "Total Movement: 1676")),
#"Cabeçalhos Promovidos" = Table.PromoteHeaders(#"Linhas Filtradas", [PromoteAllScalars=true]),
#"Outras Colunas Removidas" = Table.SelectColumns(#"Cabeçalhos Promovidos",{"Last Name", "First Name", "Employer", "Occupation", "Shift", "Movement Time", "From Location", "To Location", "Type"}),
#"Colunas Mescladas" = Table.CombineColumns(#"Outras Colunas Removidas",{"First Name", "Last Name"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Full Name"),
#"Tipo Alterado" = Table.TransformColumnTypes(#"Colunas Mescladas",{{"Movement Time", type datetime}}),
#"Linhas Classificadas" = Table.Sort(#"Tipo Alterado",{{"Full Name", Order.Ascending}, {"Movement Time", Order.Ascending}}),
#"Linhas Filtradas1" = Table.SelectRows(#"Linhas Classificadas", each ([Type] = "A")),
#"Índice Adicionado" = Table.AddIndexColumn(#"Linhas Filtradas1", "Índice", 1, 1, Int64.Type),
#"Tempo do Próximo Movimento" = Table.AddColumn(#"Índice Adicionado", "Next Movement Time", each try #"Índice Adicionado"[Movement Time]{[Índice]} otherwise null),
#"Permanência Adicionada" = Table.AddColumn(#"Tempo do Próximo Movimento", "Tempo Permanência", each
if [To Location] = "PMXL1" and [From Location] = "AQUARIUS BRASIL" then Duration.TotalMinutes([Next Movement Time] - [Movement Time]) else null),
#"Linhas Filtradas1" = Table.SelectRows(#"Permanência Adicionada", each ([Tempo Permanência] <> null)),
Personalizar1 = Table.Group(#"Linhas Filtradas1", {"Full Name", "Movement Time"}, {{"Tempo Total (Minutos)", each List.Sum([Tempo Permanência]), type number}})
in
Personalizar1
try
#"Índice Adicionado" = Table.Buffer(Table.AddIndexColumn(#"Linhas Filtradas", "Índice", 1, 1, Int64.Type)),
RC = Table.RowCount(#"Índice Adicionado"),
#"Tempo do Próximo Movimento" = Table.AddColumn(#"Índice Adicionado", "Next Movement Time", each if [Índice]<RC then #"Índice Adicionado"[Movement Time]{[Índice]} else null),
Or - use the OFFSET function in DAX.
- Anonymous1 year agoNot applicable
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!!!- lbendlin1 year agoSuper User
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- Anonymous1 year agoNot applicable
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.