cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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"

1 ACCEPTED SOLUTION
Solution Sage

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

6 REPLIES 6
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))``

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)

See my reports and blog at https://www.datazoepowerbi.com/

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper III

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

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Solution Sage

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

Solution Sage

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.