cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
padinator
Helper I
Helper I

Replicate changing Title Cell in Column

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 1Header 2Header 3Header 4
Value 1Value 2Value 3Value 4
Value 5Value 6Value 7Value 8
nullnullnullnull
Location 2   
Header 1Header 2Header 3Header 4
Value 9Value 10Value 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

 

TitleHeader 1Header 2Header 3header 4
Location 1Value1Value2Value3Value4
Location 1Value5Value6Value7Value8
Location 2Value9Value10Value11Value12
etc.    

 

Any help is highly appreaciated!

Thanks!

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

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

View solution in original post

3 REPLIES 3
BA_Pete
Super User
Super User

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

View solution in original post

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!

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!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors