Finding previous reference number

[Back2Basics]

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? 

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

[MFelix]

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.

[Back2Basics · 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. 

[MFelix]

Hi @Back2Basics ,

Glad you could figure it out.