Forum Discussion

victorbrvs's avatar
victorbrvs
New Member
4 years ago
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

BeachQuality
 Mataracanull
Barra do GuajúGood
PavunaGood
 BaleiaGood
Barra do CamaratubaGood
Baia da Traiçãonull
 CamaratubaGood
 TambáGood
Praia do ForteGood
Baia da TraiçãoGood
 TrincheirasGood
 AcajutibiróGood
Rio Tintonull
 Barra do MamanguapeGood
Praia de CampinaGood
 Praia de OiteiroGood

 The bold values are actually city names from which the cells below them (the actual beaches) belong, and I need to make them into another column that repeats the last city's name until it encounters a null value in "Quality", then it keeps repeating the next one. The result should look like this:

CityBeachQuality
 MataracaBarra do GuajúGood
 MataracaPavunaGood
 Mataraca BaleiaGood
 MataracaBarra do CamaratubaGood
Baia da Traição CamaratubaGood
Baia da Traição TambáGood
Baia da TraiçãoPraia do ForteGood
Baia da TraiçãoBaia da TraiçãoGood
Baia da Traição TrincheirasGood
Baia da Traição AcajutibiróGood
Rio Tinto Barra do MamanguapeGood
Rio TintoPraia de CampinaGood
Rio Tinto Praia de OiteiroGood

I've tried if statements, but I don't know how to make the cell repeat itself under a condition. Any idea?

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    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.

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    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.