Replicate changing Title Cell in Column

Hello again padinator ,

Try this code in Power Query. It's not the prettiest, but you'll be able to follow the steps so you can see the logic in action. You can always refine the code later.

NOTE: I've used the List.Contains function twice - once in the Replaced Value step, once in the Filtered Rows step. Here you will need to replace "Location 1" and "Location 2" with your list of five location names.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8slPTizJzM9TMFTSUYKiWJ1oJY/UxJTUIrAolGmEYBojmCZg1WGJOaWpYMUQlhGcZQxnIas0hYuawVnmcJYFWCWqe+DuBBmtAMfkOtUSbpmhAYKJ8IChkVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t]),
    repBlankNull = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"A", "B", "C", "D"}),
    #"Changed Type" = Table.TransformColumnTypes(repBlankNull,{{"A", type text}, {"B", type text}, {"C", type text}, {"D", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "A", "Location"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column", each [Location], each if not List.Contains({"Location 1", "Location 2"}, [Location]) then null else [Location],Replacer.ReplaceValue,{"Location"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Location"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each not List.Contains({"Location 1", "Location 2"}, [A]) and [A] <> null),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
    #"Filtered Rows1" = Table.SelectRows(#"Promoted Headers", each [Header 1] <> "Header 1"),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"Location 1", "Header 1", "Header 2", "Header 3", "Header 4"})
in
    #"Reordered Columns"

Pete