Power query: How to reference the row below a key word

[MichaelF1]

I'm trying to manipulate some data from an accounting spreadsheet.

I need to retain all lines that include the word "Budget" and the line directly below that word.

What I have:

(The cells xxxx are numbers that need to be deleted)

Date	Column1
01/07/2023	Budget
01/07/2023	67250
01/07/2023	xxxx
01/07/2023	xxxx
01/07/2023	xxxx
02/07/2023	Budget
02/07/2023	67100
02/07/2023	xxxx
02/07/2023	xxxx
02/07/2023	xxxx
02/07/2023	xxxx
03/07/2023	Budget
03/07/2023	67000
03/07/2023	xxxx
04/07/2023	Budget
04/07/2023	67500
04/07/2023	xxxx
04/07/2023	xxxx

What I need:

01/07/2023	Budget
01/07/2023	67250
02/07/2023	Budget
02/07/2023	67100
03/07/2023	Budget
03/07/2023	67000
04/07/2023	Budget
04/07/2023	67500

Thanks in advance 🙂

[mlsx4 · Accepted solution]

Hi @MichaelF1 

It could be a little messy but this is my approach:

let
    Origen = Excel.Workbook(File.Contents("C:\Users\Equipo2\Downloads\ex.xlsx"), null, true),
    Hoja1_Sheet = Origen{[Item="Hoja3",Kind="Sheet"]}[Data],
    #"Encabezados promovidos" = Table.PromoteHeaders(Hoja1_Sheet, [PromoteAllScalars=true]),
    #"Tipo cambiado" = Table.TransformColumnTypes(#"Encabezados promovidos",{{"Date", type date}, {"Column1", type any}}),
    IndexStep = Table.AddIndexColumn(#"Tipo cambiado", "Index", 0, 1, Int64.Type),
    #"Personalizada agregada" = Table.AddColumn(IndexStep, "personalizado", each try if [Column1]="Budget" then IndexStep[Column1]{[Index] +1} else null otherwise null),
    #"Rellenar hacia abajo" = Table.FillDown(#"Personalizada agregada",{"personalizado"}),
    #"Tipo cambiado1" = Table.TransformColumnTypes(#"Rellenar hacia abajo",{{"personalizado", type text}, {"Column1", type text}}),
    #"Columna condicional agregada" = Table.AddColumn(#"Tipo cambiado1", "Personalizado.1", each if [Column1] = "Budget" then [Column1] else if [Column1] = [personalizado] then [Column1] else null),
    #"Filas filtradas" = Table.SelectRows(#"Columna condicional agregada", each ([Personalizado.1] <> null)),
    #"Columnas quitadas" = Table.RemoveColumns(#"Filas filtradas",{"Index", "Personalizado.1", "personalizado"})
in
    #"Columnas quitadas"

1. Add index column

2. Add custom column

3. Fill down:

4. Change both types to text

5. Add a conditional column to check if it is budget or the same value:

6. Filter null values:

7. Remove unnecessary columns:

I don't know if it makes the trick but...that's the only thing that comes to my mind

[MichaelF1]

Hi @Anonymous That worked great, thanks very much!

[MichaelF1]

Hi @Idrissshatila,  Unfortunately I need this to be a dynamic process as more data will be added in the future. Sorry, probably should have mentioned this in the original post.

[Idrissshatila]

Hello @MichaelF1 ,

an easy way would be to filter out the rows you don't need.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote For my Idea💡