Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
awitt
Helper III
Helper III

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"  

 

awitt_0-1597151959017.png

 

1 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!

 

 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


View solution in original post

6 REPLIES 6
DataZoe
Microsoft Employee
Microsoft Employee

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

 

 

ordertrack.JPG

 

 

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/

Greg_Deckler
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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!

 

 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

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!



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.