Replace null values with different values for each row

Power Query
1

Good morning,

I was searching some information for doing this but I couldn’t find anything.

In the image we have a list of places in the first column and “nulls” in the second column.

image
image584×763 16.3 KB

I would like to know if I can choose an specific word of each row from the first column to replace his correspondent null in the second one.

For example …

  1. “Agrupamento de Escolas da Sé” → Sé
  2. “Agrupamento de Escolas de Ferreiras” → Ferreiras
  3. “Agrupamento de Escolas de Moimenta da Beira” → Moimenta da Beira

And so on… Any idea?

The pbix file is also attached.

Exercise.pbix (585.1 KB)

2

check for spaces at the end of the each row. You can do a trim on the column in power query. You like have spaces at the end

thanks
Keith

3

Maybe I didn’t explain myself in a proper way.

I don’t want to replace the values of the first column.

What I want is to replace each “null” of the second column with an specific word of his same row from the first column.

5

Answer:

Create a Replacements table, you can use “enter data” for that.

image

Here’s the code that was generated and you can copy that into a new blank query. And if you need to update it to include more values press the gear wheel on the Source step to modify its contents.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckwvKi1IzE3NK8lXSElVcC1Ozs9JLFZISVQIPrxSSUcJRMbq4FaXquCWWlSUmlmUWAxUjWDj1+ObnwkSTgTZ4wTSANSLKRYbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LookFor = _t, ReplaceBy = _t])
in
    Source

With this Replacements table in place (make sure to rename it to that!), copy this example into a new blank query. I’ve documented the code for your understanding.

let
    Old = List.Buffer( Replacements[LookFor] ), // QueryName[ColumnName]
    New = List.Buffer( Replacements[ReplaceBy] ), // QueryName[ColumnName]
    Source = Table.FromRows(
        {
            {"Imprensa Nacional - Casa da Moeda, S.A.", "Lisboa", "Portugal"},
            {"Centro Hospitalar de Lisboa Ocidental, EPE (CHLO)", "Lisboa", "Portugal"},
            {"BANCO DE PORTUGAL", "Lisboa", "Portugal"},
            {"Centro Hospitalar de Tondela - Viseu, E.P.E.", "Viseu", "Portugal"},
            {"Agrupamento de Escolas da Sé", null, "Portugal"},
            {"CENTRO HOSPITALAR DE ENTRE O DOURO E VOUGA, E.P.E.", "Santa Maria Da Feira", "Portugal"},
            {"Agrupamento de Escolas de Águeda", "Águeda", "Portugal"},
            {"Agrupamento de Escolas Campo Aberto", "Póvoa De Varzim", "Portugal"},
            {"Município de Vale de Cambra", "Vale De Cambra", "Portugal"},
            {"Agrupamento de Escolas de Real, Braga", "Braga", "Portugal"},
            {"Agrupamento de Escolas de Ferreiras", null, "Portugal"},
            {"Agrupamento de Escolas Sá de Miranda, Braga", "Braga", "Portugal"},
            {"Escola Secundária de Caldas das Taipas, Guimarães", "Caldas Das Taipas", "Portugal"},
            {"Penafiel Verde EM", "Penafiel", "Portugal"},
            {"Agrupamento de Escolas de Parede, Cascais", "Parede", "Portugal"},
            {"FEIRA VIVA - CULTURA E DESPORTO, E.M.", null, "Portugal"},
            {"Agrupamento de Escolas Visconde de Juromenha", "Sintra", "Portugal"},
            {"Agrupamento de Escolas de Alcanena", null, "Portugal"},
            {"Município de Gondomar", "Gondomar", "Portugal"},
            {"Agrupamento de Escolas de Celorico de Basto", "Gémeos Cbt", "Portugal"},
            {"Agrupamento de Escolas de Valdevez", "Arcos De Valdevez", "Portugal"},
            {"Agrupamento de Escolas de Mirandela", null, "Portugal"},
            {"Município de Vagos", "Vagos", "Portugal"}
        }, type table [Company Name= text, Region Market=text, Country Market=text]
    ),
    ReplValues = Table.ReplaceValue( 
        Source, //input table
        each ([Region Market] = null and List.Contains(Old, [Company Name])), // logical test
        each New{List.PositionOf(Old, [Company Name])}, // replacement value
        (x, y, z) => if y then z else x, // old, ifTrue, new
        {"Region Market"} // column to replace values in
    )
in
    ReplValues

I hope this is helpful.

2 Likes