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

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

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