Forum Discussion

Patryk92's avatar
Patryk92
New Member
3 years ago

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

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 1Column 2Column 3
Customer Number / Name123456Customer Name 1
Branch Number / Name123Branch Name 1
Account Number / NamePL65654565456545123Customer Name 1
Customer Number / Name765454Customer Name 2
Branch Number / Name456Branch Name 2
Account Number / NameGB76487241309183091Customer Name 2
Customer Number / Name345785Customer Name 3
Branch Number / Name654Branch Name 3
Account Number / NameAU63817263871236871Customer Name 3
Customer Number / Name567898Customer Name 4
Branch Number / Name467Branch Name 4
Account Number / NameDE38712312398217398Customer Name 4
Customer Number / Name567888Customer Name 5
Branch Number / Name765Branch Name 5
Account Number / NameUS83719873981273981Customer Name 5
Customer Number / Name123411Customer Name 6
Branch Number / Name999Branch Name 6
Account Number / NameNO73618239812739812Customer Name 6
Customer Number / Name555555Customer Name 7
Branch Number / Name888Branch Name 7
Account Number / NameSE87391827398172933Customer Name 7
Customer Number / Name876766Customer Name 8
Branch Number / Name777Branch Name 8
Account Number / NameJP839712983718923991Customer Name 8
Customer Number / Name878666Customer Name 9
Branch Number / Name666Branch Name 9
Account Number / NamePL87198479849223422Customer Name 9

 

 

TO-BE
Account NumberBranch NumberCustomer Name
PL65654565456545123123Customer Name 1
GB76487241309183091456Customer Name 2
AU63817263871236871654Customer Name 3
DE38712312398217398467Customer Name 4
US83719873981273981765Customer Name 5
NO73618239812739812999Customer Name 6
SE87391827398172933888Customer Name 7
JP839712983718923991777Customer Name 8
PL87198479849223422666Customer Name 9

 

Need Help

1 Reply

  • Jakinta's avatar
    Jakinta
    Solution Sage
    3 years ago

    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