Forum Discussion
Solved
How to create a column that repeat the previous value until a condition is fulfilled?
I've extracted a table from a PDF file and it looks like this Beach Quality Mataraca null Barra do Guajú Good Pavuna Good Baleia Good Barra do Camaratuba Good Baia da Tr...
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Trimmed Text" = Table.TransformColumns(Source,{{"Beach", Text.Trim, type text}}), #"Added Custom" = Table.AddColumn(#"Trimmed Text", "Custom", each if [Quality]="null" then [Beach] else null), #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Quality] = "Good")), #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Custom", "Beach", "Quality"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "City"}}) in #"Renamed Columns"Hope this helps.
Hi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Trimmed Text" = Table.TransformColumns(Source,{{"Beach", Text.Trim, type text}}),
#"Added Custom" = Table.AddColumn(#"Trimmed Text", "Custom", each if [Quality]="null" then [Beach] else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Quality] = "Good")),
#"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Custom", "Beach", "Quality"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "City"}})
in
#"Renamed Columns"Hope this helps.
It worked perfectly, thank you very much Ashish!
You are welcome.