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 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"  

 

 

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!

     

     

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      awitt
      Icon for Helper III rankHelper III
      6 years ago

      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. 

      • richbenmintz's avatar
        richbenmintz
        Icon for Resident Rockstar rankResident Rockstar
        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"