Power Query - taking subheadings out of column and adding to rows

[PQQ__]

I have a Power Query which uses a spreadsheet that looks something like the first image below. Currently I have to add a new column and manually pull out the subheadings (between the ********************* rows) and paste them next to the corresponding rows (image 2) before putting it into the power query. 

I have been attempting to do this step in power query but can't seem to figure it out. So far I've tried using an index and running totals, a List.First(List.RemoveNulls, and List.Generate but nothing has worked for me. Does anyone know how I can do this? 

Thank you in advance! 

[Rufyda]

I'm glad you found a solution! We're here in the community to support each other.
Regards,
Rufyda Rahma | MIE

[PhilipTreacy · Accepted solution]

Hi @PQQ__ 

Download example PBIX file with the code below

This approach just uses some Custom Columns

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVFNC4JAEP0ri6cKLxX9APMjIcvFVqHEg9iiku2AroT/PvVgm2yIDXOYeW9g3psJQ0VR+4zUUFkJIcDYQrbmOJfpSaH0AJ5o3XY+ezB4sbZyeuKY8ySjrJJQkp2IAI+LbnixQQ4kMc+BVUvJwh+SLNc1kO76Hpkl3waODEi/VRo9ta/LlJYdo7cxwHpGaUXHqJVXWdtgF58GmzgueYMsgPuYkeoWTrD76wRY88gVBebZN+f98AYg+RIu4iYtoWadfEIOHwKgmPhq7zygrKaCq+3YVfQG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LOCATION = _t, KEY = _t, STATUS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LOCATION", type text}, {"KEY", type text}, {"STATUS", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if Text.Contains([LOCATION], "**") then #"Added Index"[LOCATION]{[Index] - 1} else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "TYPE", each if [STATUS] <> "" then [Custom] else ""),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "Custom"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"TYPE", "LOCATION", "KEY", "STATUS"})
in
    #"Reordered Columns"

I'm curious what the end goal is with all of this though, the data you're working with isn't in a great layout for reporting/analysis.

Regards

Phil

[PQQ__]

Thanks Phil! Thats far simpler than I was making it! And in answer to your question, the data is for an import but I agree its not a great layout! 

Thanks again 🙂 

[m_dekorte]

Hi @PQQ__ 

Create a new blank query, give that query this name: addTypeCol
Open the advanced editor and replace everything you see there with this code

(tbl as table, colName as text, optional newColName as text) as table =>
let
    newCol = newColName ?? "TYPE",
    lst = try Table.Column(tbl, colName) otherwise error " No column with the name " & """" & colName & """" & " was found.",
    l = List.Buffer(List.Positions(lst)),
    c = List.Accumulate(
        l,
        [v = null, r = {}],
        (s, a) => let val = lst{a} in [
            v = if Text.Length(val ?? "") >1 and (try Text.StartsWith(lst{a+1} ?? "", "*****") otherwise false) then val else s[v],
            r = s[r] & {
                if List.AnyTrue( {Value.Equals(val, null), Text.StartsWith(val ?? "", "*****"), val = v, 
                    Value.Equals(val, ""), Text.Contains(val ?? "", " total", Comparer.OrdinalIgnoreCase)}) 
                then null
                else s[v]
            }
        ]
    )[r],
    ans = Table.RenameColumns( 
        Table.FromColumns(
            Table.ToColumns(tbl) & {c},
            Value.Type(tbl & #table(type table[XXX=text], {}))
        ), {"XXX", newCol}
    )
in 
    ans

Now you can invoke this function by selecting a table and providing the name for the column that contains the subheadings in your example LOCATION then you hit invoke. That should do it. Optionally you can give the new column that is added a different name, by default it will be named TYPE.

A new query is created, that will look something like this.

Hope this is helpful