String Based New Column

[awitt]

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"  

[DataZoe]

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"

[Greg_Deckler]

@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.

[awitt]

@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. 

[Greg_Deckler]

@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...

[richbenmintz · Accepted 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!

[richbenmintz]

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!