Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Fred LaValley]

?How do I remove specific text/characters only from the end of string? I want to remove each occurrence of “-1” from a string of numbers, but only when it ends with “-1”. What makes it more complicated is that in this list of Order Numbers, I also have some that have a “-1” in the middle of the Order Number, and not just on the end. So I can’t just find and remove all occurrences of “-1” because I will lose information that I need. Also, the Order Numbers are sometimes 7 digits long, and sometimes only 5, and sometimes up to 9 digits long.

1234567
1234567-1
011-98765
011-98765-1
45678
45678-1

The freight costs are divided up among all the variations of the Order Number. My goal is to be able to eliminate all the variations of the Order Numbers, but still be able to capture all the associated freight costs without losing data. Currently I have to select all the variations in order to do see all the freight costs, and that results in inefficiency and mistakes.

Please help!

——————————
FL
Oakwood GA
——————————

[Olivier Aillery]

Hi,

Did you try with “Split Colums”, using the most right delimeter option?
Using a personalised delimiter being “-1″…

Is it working ?

——————————
Olivier
——————————
——————————————-

[Fred LaValley]

I’m not sure how to restrict it so that it only splits it by the delimiter when it appears at the end of the order number, and not in the middle also.

 

011-12345

011-12345-1

1234567

1234567-1

 

How do I make sure it does not split it and become:

011                         12345                   

011                         12345                    1

 

 

 

I want it to be:

 

011-12345

011-12345

1234567

1234567

 

And the “-1” to just be removed if they appear at the end only. How do I only limit it to the end of the string?

 

 

Thanks!

 

Fred LaValley

Finance Analyst – Prepared Foods

Wayne Farms LLC

Oakwood, GA

770-297-3423

 

——Original Message——

Hi,

Did you try with “Split Colums”, using the most right delimeter option?
Using a personalised delimiter being “-1″…

Is it working ?

——————————
Olivier
——————————

[Olivier Aillery]

Hi,

You’re right it is not that simple…
Maybe you can extract the 2 last caracters in a column, and then test if they are -1.
Something like :
IF(RIGHT(String,2)=”-1″ then…
If so, you keep x caracters from left :
x, being total_length – 2.
Would that help ?

——————————
Olivier
——————————
——————————————-

[Fred LaValley]

I’ll give it a shot. I’m still very new to DAX and all of this, so I’m stretching my brain each time I attempt to do anything. I’ll let you know in a little bit. Thank you so far for your time!

 

Fred LaValley

Finance Analyst – Prepared Foods

Wayne Farms LLC

Oakwood, GA

770-297-3423

 

——Original Message——

Hi,

You’re right it is not that simple…
Maybe you can extract the 2 last caracters in a column, and then test if they are -1.
Something like :
IF(RIGHT(String,2)=”-1″ then…
If so, you keep x caracters from left :
x, being total_length – 2.
Would that help ?

——————————
Olivier
——————————

[Fred LaValley]

I don’t know enough about DAX to figure out how to do what you suggested. Also, it keeps saying IF and RIGHT are not recognized ???

 

So I made a column that only keeps the data/characters before the 2^nd delimiter, and it discards the data after the 2^nd delimiter.

 

= Text.BeforeDelimiter([Order Number], “-“, 1)

 

So this eliminated all the 011-12345-1 and 011-12345-2, but I’m now left with:

 

011-12345

1234567-1

 

I can’t use the delimiter function again unless I can find a way to only split it when it’s the 8^th character in the string, or 2^nd from the right. How can I do this? I also want to make sure the 1 (after the -) is removed as well, so that all I’m left with is 1234567.

 

 

 

Fred LaValley

Finance Analyst – Prepared Foods

Wayne Farms LLC

Oakwood, GA

770-297-3423

 

——Original Message——

Hi,

You’re right it is not that simple…
Maybe you can extract the 2 last caracters in a column, and then test if they are -1.
Something like :
IF(RIGHT(String,2)=”-1″ then…
If so, you keep x caracters from left :
x, being total_length – 2.
Would that help ?

——————————
Olivier
——————————

[Olivier Aillery]

Hi,

As you certainly know, you have 2 kind of formulas in PBI :
language DAX (in Power Bi Desktop)
and language M in Power Query.
The formula just above (= Text.BeforeDelimiter([Order Number], “-“, 1) is M code, not DAX.

My solution was in DAX, and shoud: be in a New Column (Add Column via tab Model) :
First column could be :

Last 2 car = RIGHT(Saisie[Client];2)

A test could be :

Test01 = IF(Saisie[Last 2 car]="-1";"OK";"Pas OK")?

And finally :

Final = IF(Saisie[Test01]="OK";LEFT(Saisie[Client];LEN(Saisie[Client])-2);Saisie[Client])?

As you can imgaine, you can simplify this, but at least you have the way 😉

Hope it helps,

——————————
Olivier
——————————
——————————————-

[John Thomas]

Add a custom column:
if Text.End([ID Column],2)=”-1″ then Text.Start([ID Column],Text.Length([ID Column])-2) else [ID Column]

——————————
John Thomas
Director of Project Controls and Estimating
Huntersville NC
980-287-2614
——————————
——————————————-

[Fred LaValley]

I ended up using the Add Column from Examples and edited a previous column with the order numbers in it. Originally, the Order Numbers reported in even more “convoluted” fashion:

1234567,1234567-1,1234568,1234568-1.

So I had to separate them by the delimiter and then parse them down to have their own row/cell. After that I had no idea what to do.

 

This is what the formula/language looks like:

= Table.AddColumn(#”Trimmed Text1″, “Final Order Number List”, each let splitOrderNumOnly = Splitter.SplitTextByDelimiter(“-“, QuoteStyle.None)([Order Num Only]) in Text.Combine({Text.Start([Order Num Only], 6), Text.Middle(splitOrderNumOnly{1}?, 2), Text.Middle(splitOrderNumOnly{0}?, 6, 1)}), type text)

 

Good news is that it’s working now! The problem is I have no idea what all that really means LOL

 

I’ve been using BI for almost 1 year now, and I’m basically self-taught on all this, so I’m kind of slow in comprehending DAX and M and everything. I’m beginning to understand it via the solutions you and others have provided, which is the real value from this experience!

 

Thanks for the help!!!!!

 

 

Fred LaValley

Finance Analyst – Prepared Foods

Wayne Farms LLC

Oakwood, GA

770-297-3423

 

——Original Message——

Add a custom column:
if Text.End([ID Column],2)=”-1″ then Text.Start([ID Column],Text.Length([ID Column])-2) else [ID Column]

——————————
John Thomas
Director of Project Controls and Estimating
Huntersville NC
980-287-2614
——————————