Forum Discussion
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 -
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.
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 AddCustomerCopy 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
5 Replies
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.
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 AddCustomerCopy 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
JimmyThank 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.
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
