Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Simon_D
Frequent Visitor

Cleaning up unstructured excel purchase orders

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 -

POexample.JPG

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

 

POfinal.JPG

 

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.

1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

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

View solution in original post

5 REPLIES 5
Simon_D
Frequent Visitor

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
Community Champion
Community Champion

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
Community Champion
Community Champion

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
Super User
Super User

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
Impactful Individual
Impactful Individual

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!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors