Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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:
This gave me what I wanted. There might be a cleaner way of doing it, but it had the desired effect.
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:
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks 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:
This gave me what I wanted. There might be a cleaner way of doing it, but it had the desired effect.
Hi @Back2Basics ,
Glad you could figure it out.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |