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

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