Excel and Power Query, order altered when new data gets added on refresh

[Anonymous]

Greetings, i couldnt find a dedicated Power query and Excel forum, so ill try to ask here.

I have data collected from an email (some platform notifications) that i use in Power BI, but users need to mark if they reviewed that notification and leave a link to a document attached. So i had to make a separate Excel sheet that loads data with Power query the same way the Power BI file does.

Now since i had to add 2 new columns to the loaded data in the Sheet, whenever new data loads in a refresh, all the info in the new columns swifts up.

Today i loaded 4 new notifications and the values on that first 2 columns (added after data loads from query) shifts up from 1 position above last value. Before i had an reorder column step on my query, but that made the order on the new columns invert each time i made a refresh (wich was a lot weirder), i searched online and they recommend not to order columns in query, so i removed it, and added an inverse count index, but now the behaviour is like i described before.

Is there any way to fix it? another step i shouldnt be doing on query? maybe i could add the new columns in query too? maybe filtering the data to current year is also messing it up?

Here is the query code, just some columns added, current year filter and index column

let
    Origen = Exchange.Contents("**mail adress**.com"),
    Mail1 = Origen{[Name="Mail"]}[Data],
    #"Filas filtradas" = Table.SelectRows(Mail1, each ([Folder Path] = "\Bandeja de entrada\PGP\")),
    #"Texto extraído entre delimitadores" = Table.TransformColumns(#"Filas filtradas", {{"Folder Path", each Text.BetweenDelimiters(_, "\", "\", 1, 0), type text}}),
    #"Columnas quitadas" = Table.RemoveColumns(#"Texto extraído entre delimitadores",{"Sender", "DisplayTo", "DisplayCc", "ToRecipients", "CcRecipients", "BccRecipients", "Categories", "Id", "Attributes"}),
    #"Se expandió Body" = Table.ExpandRecordColumn(#"Columnas quitadas", "Body", {"TextBody"}, {"Body.TextBody"}),
    #"Texto insertado entre delimitadores" = Table.AddColumn(#"Se expandió Body", "Enlace PGP", each Text.BetweenDelimiters([Body.TextBody], "<", ">"), type text),
    #"Texto insertado antes del delimitador" = Table.AddColumn(#"Texto insertado entre delimitadores", "NUP", each Text.BeforeDelimiter([Subject], " "), type text),
    #"Texto insertado entre delimitadores1" = Table.AddColumn(#"Texto insertado antes del delimitador", "Proyecto", each Text.BetweenDelimiters([Subject], "|", "|"), type text),
    #"Texto insertado entre delimitadores2" = Table.AddColumn(#"Texto insertado entre delimitadores1", "Clasif 1", each Text.BetweenDelimiters([Subject], "| ", " |", 1, 0), type text),
    #"Texto insertado entre delimitadores3" = Table.AddColumn(#"Texto insertado entre delimitadores2", "Tipo", each Text.BetweenDelimiters([Subject], "| ", " |", 2, 0), type text),
    #"Texto insertado después del delimitador" = Table.AddColumn(#"Texto insertado entre delimitadores3", "Estado", each Text.AfterDelimiter([Subject], "| ", {0, RelativePosition.FromEnd}), type text),
    #"Filas filtradas1" = Table.SelectRows(#"Texto insertado después del delimitador", each Date.IsInCurrentYear([DateTimeSent])),
    #"Índice agregado" = Table.AddIndexColumn(#"Filas filtradas1", "Índice", Table.RowCount(#"Filas filtradas1"), -1, Int64.Type),
    #"Columnas reordenadas" = Table.ReorderColumns(#"Índice agregado",{"Índice", "Folder Path", "Subject", "DateTimeSent", "DateTimeReceived", "Importance", "IsRead", "HasAttachments", "Attachments", "Preview", "Body.TextBody", "Enlace PGP", "NUP", "Proyecto", "Clasif 1", "Tipo", "Estado"}),
    #"Added Custom" = Table.AddColumn(#"Columnas reordenadas", "Clasificación", each **a long switch function to search for some keywords**,
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Índice", "Folder Path", "Subject", "DateTimeSent", "DateTimeReceived", "Importance", "IsRead", "HasAttachments", "Attachments", "Preview", "Body.TextBody", "Enlace PGP", "NUP", "Proyecto", "Clasificación", "Tipo", "Estado"}),
    #"Valor reemplazado" = Table.ReplaceValue(#"Reordered Columns","Aprob...","Aprobación Manual",Replacer.ReplaceText,{"Estado"}),
    #"Valor reemplazado1" = Table.ReplaceValue(#"Valor reemplazado","Carga...","Carga de Archivo",Replacer.ReplaceText,{"Estado"}),
    #"Personalizada agregada1" = Table.AddColumn(#"Valor reemplazado1", "Id estado", each if Text.Contains([Estado],"carga",Comparer.OrdinalIgnoreCase) then 1 else
if Text.Contains([Estado],"aprobación",Comparer.OrdinalIgnoreCase) then 2 else
if Text.Contains([Estado],"rechazada",Comparer.OrdinalIgnoreCase) then 3 else
""),
    #"Tipo cambiado" = Table.TransformColumnTypes(#"Personalizada agregada1",{{"Id estado", Int64.Type}}),
    #"Personalizada agregada2" = Table.AddColumn(#"Tipo cambiado", "Mes", each Date.MonthName([DateTimeSent])),
    #"Mes insertado" = Table.AddColumn(#"Personalizada agregada2", "Mes.1", each Date.Month([DateTimeSent]), Int64.Type),
    #"Columnas con nombre cambiado" = Table.RenameColumns(#"Mes insertado",{{"Mes.1", "Mes num"}, {"DateTimeReceived", "Fecha recibido"}})
in
    #"Columnas con nombre cambiado"

[DanS446]

Even when letting the new data on the bottom new rows are still put above the last one, before it was filled with "0" until the last row, and now after a refresh the new 4 rows are above last row, this means the data written in excel on the 2 new columns added after the query table loads. Im looking into some way of self referencing the table so it writes the new columns to the query, seems a bit complex but will report later.

[Anonymous]

Hi @Anonymous 

Sorry, I don't quite understand the problem you are experiencing. Do you hope to always have the newly added rows at the bottom? If so, this cannot be guaranteed. In Power Query, every time when you refresh the data, it will load all data from data sources to replace all existing loaded data in the model. So it doesn't know which rows are newly added in the data source. When storing data, it follows columnar storage instead of row storage, so it doesn't care about the row ordering, which means the order of rows may change.

If you want the rows to display in a specific order, you have to add an explicit step to sort rows by one or more columns. From your screenshot, it seems the rows are sorted by the fourth datetime-type column currently. You probably need to sort rows by some other columns to have the desired outcome you want. But note that it cannot guarantee the latest added rows are at the bottom unless you have a column recording Edit time or Recorded time and sort rows by this time column. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[DanS446]

The data is notifications that comes in every day, so the order is current date > older date. Before i had them ordered on date from newest to oldest just in case, but that made the new columns order invert on each refresh, like those attached files on column 2 would jump to the first rows, then to the bottom on each refresh.

As you say the problem seems to be that new data pushes old data down, but excel doesnt know what to do with the current columns, so it just creates blanks at the bottom, not exactly the bottom since it leaves the last row as it was.

I tried adding the new columns on the query but data gets overwriten on refresh, now i tried to inverse the order on query, so new data gets added on the bottom rather than push down the previous rows, not ideal tho since i needed to see the data from newest to oldest. Ill check once a new notification arrives if it keeps the order.

Any other idea how could i tackle this? I need to add info in other columns for each row but they need to stay with that row when new data gets added.