Extract data using PQ when column names are occurring and placed vertically

[Patryk92]

Hi Experts

Looking for any advice on below case.
This is layout of extract I get from external system and it cannot be changed anyhow.

Each 3 rows store 6 information like:
Customer Number
Customer Name
Branch Number
Branch Name

Account Number
Account Name

I just need to extract:
Account Number

Branch Number

Customer Name

Layout is soooo mixed, that I have no idea where to start 😞

Would that be possible to accomplish that using Power Query only?
Or shall I also prepare some VBA coding to change the layout a little bit, before I load that to PQ??

I would be grateful for any advice on that.

AS-IS
Column 1	Column 2	Column 3
Customer Number / Name	123456	Customer Name 1
Branch Number / Name	123	Branch Name 1
Account Number / Name	PL65654565456545123	Customer Name 1
Customer Number / Name	765454	Customer Name 2
Branch Number / Name	456	Branch Name 2
Account Number / Name	GB76487241309183091	Customer Name 2
Customer Number / Name	345785	Customer Name 3
Branch Number / Name	654	Branch Name 3
Account Number / Name	AU63817263871236871	Customer Name 3
Customer Number / Name	567898	Customer Name 4
Branch Number / Name	467	Branch Name 4
Account Number / Name	DE38712312398217398	Customer Name 4
Customer Number / Name	567888	Customer Name 5
Branch Number / Name	765	Branch Name 5
Account Number / Name	US83719873981273981	Customer Name 5
Customer Number / Name	123411	Customer Name 6
Branch Number / Name	999	Branch Name 6
Account Number / Name	NO73618239812739812	Customer Name 6
Customer Number / Name	555555	Customer Name 7
Branch Number / Name	888	Branch Name 7
Account Number / Name	SE87391827398172933	Customer Name 7
Customer Number / Name	876766	Customer Name 8
Branch Number / Name	777	Branch Name 8
Account Number / Name	JP839712983718923991	Customer Name 8
Customer Number / Name	878666	Customer Name 9
Branch Number / Name	666	Branch Name 9
Account Number / Name	PL87198479849223422	Customer Name 9

TO-BE
Account Number	Branch Number	Customer Name
PL65654565456545123	123	Customer Name 1
GB76487241309183091	456	Customer Name 2
AU63817263871236871	654	Customer Name 3
DE38712312398217398	467	Customer Name 4
US83719873981273981	765	Customer Name 5
NO73618239812739812	999	Customer Name 6
SE87391827398172933	888	Customer Name 7
JP839712983718923991	777	Customer Name 8
PL87198479849223422	666	Customer Name 9

[Jakinta]

You can try with steps below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZRNa4NAEIb/ingOtPs1H8ekDYVSbCDkJDmkEujFBNL4/zurtIrrTsQdBfd1nn13Zuu6fOl+7tf2fCuqrv2Sx1NRndpzuSqNdT6AvIwz5ENhyuOqLje306X5XtJI/Ps4Tl83zbW73JP5uw8IEPz/GPRLCbOYGHU+UVkNc1jXFNOqmG8bBE9ovXHPbCiGTMIspniJFBKV0zChX9cU06mY6wM4MmglojgJEjMJs5gBkJgSlVfdBJxhehXzdTvwyc1kDbpsQhWTUlXQMKVQZphBxTzsyaFhinzG9jGTUG0hk6pAw2TmGSaomNUnOjBkR0qbSZh3s78SFWqYg/1TTFQx99topHD2iGjZpZ2OOiYhIKQHEqmbjvPaJBXzfUeOpTg57j2xuLrQ6vSIk2CBk9VWh/mJxA8OToq16VEGWykzm+66/OH4Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, #"Column 2" = _t, #"Column 3" = _t]),
    AccBr = List.Transform(List.Split( Source[Column 2], 3), each {_{2}} &{_{1}}),
    Customer = List.Transform(List.Split( Source[Column 3], 3), each {_{0}}),
    ToTable = Table.FromRows( List.Transform( List.Zip ( {AccBr,Customer}), List.Combine), {"Account Number", "Branch Number", "Customer Name"})
in
    ToTable