Cleaning up unstructured excel purchase orders

[Simon_D]

Hi,

I am looking to import purchase order excel files from sharepoint and connect it to our bill of materials database. However, the excel files contain not ideal data structure -

I would like to format the above file into below table -

I am hoping someone could point me in the right direction on what method could be used to clean up such input - can it be done with built in transform tools or custom query code is required?

Appreciate your help.

[Simon_D]

Thank you for the replies but the begginer friendly solution I have found was to create custom columns "Job Id" and "Customer ID" using formula = #"Added Custom" [ColumnNo]{0} to select values and remove top 14 rows.

[Jimmy801]

Hello @Simon_D 

this is what you can see in my solution 🙂 remove top 14 rows and adding custom column and referencing the column and row to get specific values.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Jimmy801 · Accepted solution]

Hello @Simon_D 

this is an easy task... .BUT... the solution that is working for you depends a lot on your form. It's always like this or is it changing? I can show you a simple aproach, if your form is NOT changing. Have a look

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtKBoVgdMC+gtCg5I7E4VcG/KCW1CFUOiLzykxQ8XYAMMxMzE1MgRpJzLi0uyc9NLYIoSCxOSQNiJPkAfwWXxJJUVANdUnMyy1KLKhVSMKRw8vzyS1KL8SkIqSwAmeULNLIoMzFHwTk/BYWfklqcXJRZUJKZnwfT4uYOJJyMjMyMQG5PTISJB/mCNBobGBuaARlJSUlKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    GetPurchaseOrderItems = Table.PromoteHeaders(Table.Range
    (
        Table.RemoveColumns(Source, "Column1"),
        10
    )),
    AddJobID = Table.AddColumn
    (
        GetPurchaseOrderItems,
        "Job ID",
        each Source[Column4]{2}
    ),
    AddCustomer = Table.AddColumn
    (
        AddJobID,
        "Customer",
        each Source[Column4]{3}
    )
    //etc......
in
    AddCustomer

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[HotChilli]

One strategy might be to duplicate the query and in one (query1) - keep the header info, manipulate it (clean it up and then pivot)

and query2 - keep the detail lines (it's close to the desired result already)

Then do a cross-join on the queries to smash them back together.

[ziying35]

Hi, @Simon_D 

You can desensitize your files, upload them to a cloud drive and share the link here, that will solve the problem much faster!