Forum Discussion

awitt's avatar
awitt
Icon for Helper III rankHelper III
6 years ago
Solved

String Based New Column

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 ...
Need Help
  • richbenmintz's avatar
    richbenmintz
    6 years ago

    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!

     

     

DataZoe's avatar
DataZoe
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

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"