Forum Discussion
Power query: How to reference the row below a key word
- 3 years ago
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
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
Hi Anonymous That worked great, thanks very much!