cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Helper III
Helper III

Re: Finding previous reference number

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

Re: Finding previous reference number

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





Highlighted
Helper III
Helper III

Re: Finding previous reference number

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

Highlighted
Super User V
Super User V

Re: Finding previous reference number

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
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.