Beginner Power Query User. Loaded question

[sbellmore]

First image is the data I loaded into power query from a text file. This is a learning task/problem I am really struggling with and I know its simple. Second image is where I am at. I created a duplicate querry and planned on doing table joins to get the information in the correct format. 3rd picture is the requested output. I split the columns and filtered out the undesired rows.  I am trying to find the best way forward and have been consulting google without much avail. The next column I was working on was the "Fund Center" Column. I am trying to figure out if there is a better way going about this than I currently am. If someone could give me an example or two of the first two outputted columns in picture 3 for "Fund Center/Fund" I would appreciate it as should give me more than enough head start to figuring out the rest. 

[jbwtp]

Hi @sbellmore,

The data on the screenshot is qute complex to replicate fully, but hopefully you wil lbe abe to figureout what to do from this example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlHSUXIrzUuxUjAyMDIwNDQwMgSKoKBYnWglU6gyBefUvJKiVCsFR9PgiAhsKs2QRUBagJSjWwCIzMnJL8kF6gerM0dWV1FRASQNDUAAwQApsyBOmSVxygwNiFRnSIS6WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}}),
    Fund = Text.AfterDelimiter(List.First(List.Select(#"Changed Type"[Column2], each Text.Contains(_, "Fund:"))), ": "),
    #"Fund Centre" = Text.AfterDelimiter(List.First(List.Select(#"Changed Type"[Column2], each Text.Contains(_, "Fund Centre:"))), ": "),
    SkipRows = Table.Skip(#"Changed Type", List.PositionOf(#"Changed Type"[Column6], "AFP")),
    #"Promoted Headers" = Table.PromoteHeaders(SkipRows, [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"6", Int64.Type}, {"", type text}, {"_1", type text}, {"_2", type text}, {"Fund", type text}, {"AFP", Int64.Type}, {"Allotment", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Fund.1", each Fund),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Fund Cente", each #"Fund Centre"),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Fund Cente", "Fund.1", "6", "", "_1", "_2", "Fund", "AFP", "Allotment"})
in
    #"Reordered Columns"

Kind regards,

John