Replicate changing Title Cell in Column

[padinator]

If a Table o records which is basically "interrupted" by Titles which are indicating the particular location the following records belong to. So in short the table does look somehow like this

Location 1
Header 1	Header 2	Header 3	Header 4
Value 1	Value 2	Value 3	Value 4
Value 5	Value 6	Value 7	Value 8
null	null	null	null
Location 2
Header 1	Header 2	Header 3	Header 4
Value 9	Value 10	Value 11	Value 12

So if this title would not change i could simply create another COlumn and insert the Value of Record.Field knowing the position of the first Title. But now, since the title is changing and the position of these titles are completely random (this table gets periodically extended) i have to find a generic solution where the position of a title is dybanamically identified and all following Rows are getting This Title until the next title is coming!

What should also be mentioned is that these rows where the title is actually stored in are going to be filtered out in further steps as well as the redundant Headers . Further what i do know is the Titles which MAY accur - it is a set of 5 titles which do NOT change, so I could basically somehow look out for these values in order to identify the that it is actually a title.

My wanted result as you might expect would be

Title	Header 1	Header 2	Header 3	header 4
Location 1	Value1	Value2	Value3	Value4
Location 1	Value5	Value6	Value7	Value8
Location 2	Value9	Value10	Value11	Value12
etc.

Any help is highly appreaciated!

Thanks!

[BA_Pete · Accepted solution]

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

[padinator]

Hei Pete, and thanks for your answer. I finally found a solution myself within a forum entry which i quite similar with the one suggested by your. The crucial function i was looking for was the Table.FillDown. I made it a little bit less sophisticated with a conditional Column where i was checking if Location is A,B or C else NULL) followed by the FillDown command!

anyway, thanks a lot for your support!

[padinator]

Hei Pete, and thanks for your answer. I finally found a solution myself within a forum entry which i quite similar with the one suggested by your. The crucial function i was looking for was the Table.FillDown. I made it a little bit less sophisticated with a conditional Column where i was checking if Location is A,B or C else NULL) followed by the FillDown command!

anyway, thanks a lot for your support!