Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I have data that has one record per concatenated OrderID & Tracking Number and some records have an incorrect OrderID that is throwing off the data. All correct OrderIDs start with a "1" so the line that starts with a "5" is incorrect. However the departure and current date information is relevant so I cannot simply remove the "5" line.
What I need is to create the yellow columns with a corrected OrderID applied to each record and then the min date of the two from the Departure Date column and the max date of the two from the Current Date column applied to the table.
The correct OrderID's always start with "1" and the incorrect OrderID's don't ever seem to begin with "1"
Solved! Go to Solution.
Hi @awitt,
This calculated colulmn Formula should get the correct orderId for each tracking number
Correct Order ID =
var trackingNumber = [Tracking Number]
return
CALCULATE(max('Table'[Order ID]), FILTER(ALL('Table'), 'Table'[Tracking Number] = trackingNumber && LEFT('Table'[Order ID], 1) = "1"))
Then this calc column would create the correct Order Track
Correct OrderTrack = 'Table'[Correct Order ID] & 'Table'[Tracking Number]
The Min Max Dates can then be derived with a measure or a calculated column.
Hope this helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
Proud to be a Super User!
Hi @awitt ,
You could try this:
New OrderTrack = SUMMARIZECOLUMNS(OrderTrack[TrackingNumber],"Order ID",MIN(OrderTrack[Order ID]),"Departure Date",min(OrderTrack[Departure Date]),"Current Date",max(OrderTrack[Current Date]),"Days",datediff(min(OrderTrack[Departure Date]),max(OrderTrack[Current Date]),day))
You can adjust the MIN(OrderTrack[Order ID]) to MAX() if it's not giving you the right value. I had also scrubbed out those Order ID's starting with 5's in M script using a modified version of the info here: https://stackoverflow.com/questions/54368921/find-and-replace-using-wildcard-in-power-bi
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1M7ewVNJRMowyglHIwub6hub6RgZGBmC2kQGMbawUqxOtZGpsbArCqPqRRIHaLZC0m8LZSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order ID" = _t, TrackingNumber = _t, OrderTrack = _t, #"Departure Date" = _t, #"Current Date" = _t, #"Days Between" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Order ID", Int64.Type}, {"TrackingNumber", type text}, {"OrderTrack", type text}, {"Departure Date", type date}, {"Current Date", type date}, {"Days Between", Int64.Type}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Order ID", type text}}),
replaceTextIfContains = (inputText,old,new) =>
if (inputText is text and Text.StartsWith(inputText,old)) then
new else inputText,
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1","5",null,replaceTextIfContains,{"Order ID"}),
#"Removed Other Columns" = Table.SelectColumns(#"Replaced Value",{"Order ID", "TrackingNumber", "Departure Date", "Current Date"})
in
#"Removed Other Columns"
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
@awitt - OK, so how do you know the correct order id? Sorry, I'm missing something. Is it the Tracking number? If so, would suggest doing a Merge against itself based on the tracking number and then go from there.
@Greg_Deckler The correct OrderID always starts with a 1 and the incorrect orderID never seems to start with a 1. The tracking numbers for both are always the same and correct.
So something along the lines of Case/When '1%' if you were to do this in SQL.
@awitt , @ImkeF may have a Power Query way of doing it. You could do it with DAX as @richbenmintz states. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
Hi @awitt,
This calculated colulmn Formula should get the correct orderId for each tracking number
Correct Order ID =
var trackingNumber = [Tracking Number]
return
CALCULATE(max('Table'[Order ID]), FILTER(ALL('Table'), 'Table'[Tracking Number] = trackingNumber && LEFT('Table'[Order ID], 1) = "1"))
Then this calc column would create the correct Order Track
Correct OrderTrack = 'Table'[Correct Order ID] & 'Table'[Tracking Number]
The Min Max Dates can then be derived with a measure or a calculated column.
Hope this helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
Proud to be a Super User!
Hi @awitt,
Here are the measures to get the Min Max and days between
Min Dep Date = CALCULATE(MIN('Table'[Departure Date]), FILTER(ALL('Table'), 'Table'[Correct Order ID] = MIN('Table'[Correct Order ID])))
Max Current Date = CALCULATE(Max('Table'[Current Date]), FILTER(ALL('Table'), 'Table'[Correct Order ID] = MIN('Table'[Correct Order ID])))
Days Between = DATEDIFF('Table'[Min Dep Date], 'Table'[Max Current Date], DAY)
Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
Proud to be a Super User!
User | Count |
---|---|
123 | |
70 | |
67 | |
58 | |
53 |
User | Count |
---|---|
189 | |
94 | |
67 | |
63 | |
56 |