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.
That is not necessarily a large size. But I see you have chosen the "Combine Binaries" feature. That is not optimal, and you can improve the ingestion by re-implementing the process and by using CSV files instead of XLSX (if possible).
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 Personalizar1try
#"Í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.
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!!!