Forum Discussion

MichaelF1's avatar
MichaelF1
Icon for Helper III rankHelper III
3 years ago
Solved

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

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)

 

DateColumn1
  
01/07/2023Budget
01/07/202367250
01/07/2023xxxx
01/07/2023xxxx
01/07/2023xxxx
02/07/2023Budget
02/07/202367100
02/07/2023xxxx
02/07/2023xxxx
02/07/2023xxxx
02/07/2023xxxx
03/07/2023Budget
03/07/202367000
03/07/2023xxxx
04/07/2023Budget
04/07/202367500
04/07/2023xxxx
04/07/2023xxxx

 

What I need:

 

01/07/2023Budget
01/07/202367250
02/07/2023Budget
02/07/202367100
03/07/2023Budget
03/07/202367000
04/07/2023Budget
04/07/202367500

 

Thanks in advance 🙂

  • 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

4 Replies

  • mlsx4's avatar
    mlsx4
    Icon for Memorable Member rankMemorable Member

    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's avatar
      MichaelF1
      Icon for Helper III rankHelper III

      Hi Anonymous That worked great, thanks very much!

  • 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.