Forum Discussion
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)
| 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 🙂
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
Memorable 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
Helper III
Hi Anonymous That worked great, thanks very much!
- Idrissshatila
Super User
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💡 - MichaelF1
Helper III
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.