cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Back2Basics
Helper III
Helper III

Finding previous reference number

Okay, I have a table with records that have an customer number. Each customer number can have multiple rows of orders. Some how a whole bunch of orders recently had their reference numbes over-written to '1'. Thankfully we have a history table, so I can see the previous order numbers... 

 

What I'm hoping to do is it find all of the customers who have an order reference of '1' and then to update these to the previous order reference. In the order history I have somethnig like this:

 

What I need, to import the data to correct it, is a table that has the customer history reference to be updated (eg. AB1001/2) in one column, the order reference for that record in column 2 and the previous order reference in column 3. Is there a way to do this directly in PBI or do I need to export and play in excel? 

 

CustomerOrder
AB1001/115000
AB1001/21
BC1001/114000
BC1001/2

1

1 ACCEPTED SOLUTION

Thanks for the response @MFelix but it was a bit over my head. 

 

I managed to get around this by duplicating the table a couple of times and making some changes, I'll explain what I did:

 

  • I duplicated the table and filtered so I had only the records where the order number was 1. This gave me a list of all those that needed to be corrected. we'll call this table A.
  • I duplied the original table again and then removed records where the order number was 1. I now had two tables, one with the rows that needed correcting and one with the previous order number. We'll call this table B.
  • Then, I went back to table A and duplicated the Customer column. I then split this duplicate by '/' giving me eg. AB1001 in one column and 2 in another.
  • Then I added a new column to provide the previous reference using textcombine - textcombine([customer1], ([customer2] -1), /). We'll call this 'previous customer'
  • I then closed the editor and created a relationship between table A previous customer and table B customer. 

This gave me what I wanted. There might be a cleaner way of doing it, but it had the desired effect. 

View solution in original post

3 REPLIES 3
MFelix
Super User
Super User

Hi @Back2Basics ,

 

Without knowing how you have the historical data connected to your data (what is consider the ID for a specific line) you can do a merge of both table by the specific ID.

 

Assuming that in the data you have send out that the customer AB1001/2 is the unique value and that the tables are as below:

Current

Customer Order
AB1001/1 15000
AB1001/2 1
BC1001/1 14000
BC1001/2

1

 

Historical

Customer Order
AB1001/1 15000
AB1001/2 18000
BC1001/1 14000
BC1001/2

10000

 

Now you need to do the following:

  • Make a merge of By Customer of both tables
    • The link between both tables is the Customer but  can be more columns
  • Then expand the column order with the values you need.
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnQyNDAw1DdU0lEyNDUwMFCK1YELGoEEwQJOzghVJjBVUEGoqlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Order = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Order", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Customer"}, Historical, {"Customer"}, "Historical", JoinKind.LeftOuter),
    #"Expanded Historical" = Table.ExpandTableColumn(#"Merged Queries", "Historical", {"Order"}, {"Historical.Order"})
in
    #"Expanded Historical"

 

Be awarwe that depeding on your data the way you make the merge (left, rigth, fuzzy) may need adjustments.

Check PBIX attach.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thanks for the response @MFelix but it was a bit over my head. 

 

I managed to get around this by duplicating the table a couple of times and making some changes, I'll explain what I did:

 

  • I duplicated the table and filtered so I had only the records where the order number was 1. This gave me a list of all those that needed to be corrected. we'll call this table A.
  • I duplied the original table again and then removed records where the order number was 1. I now had two tables, one with the rows that needed correcting and one with the previous order number. We'll call this table B.
  • Then, I went back to table A and duplicated the Customer column. I then split this duplicate by '/' giving me eg. AB1001 in one column and 2 in another.
  • Then I added a new column to provide the previous reference using textcombine - textcombine([customer1], ([customer2] -1), /). We'll call this 'previous customer'
  • I then closed the editor and created a relationship between table A previous customer and table B customer. 

This gave me what I wanted. There might be a cleaner way of doing it, but it had the desired effect. 

View solution in original post

Hi @Back2Basics ,

 

Glad you could figure it out.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!