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 ...
Need Help
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