Power Query formula to get text after last comma if comma exist or keep text if no comma

[Pandadev]

Hi I have data stored in a column which sometimes has historic data split by a column , with latest data always after last comma.

Sometimes though there is no historic data so then I would need to keep all the data. Is there a way in data query to do this please.

Example

A22122,D46543

B11111

C22222,C22221,C22223

What I would need is

D46543

B11111

C22223

[amitchandak]

@Pandadev , Try a new column like this power query

Text.End([col],Text.PositionOf([col], ";", Occurrence.Last))

[Pandadev]

The code nearly works , but in some instances there are codes which can have different amounts of digits .

some are 5 digits , some 6 , some 7 etc. with this code , it looks like it is only giving last 6 characters .

[vivran22 · Accepted solution]

@Pandadev

You can then try Text After Delimiter

Use the following settings:

Cheers!
Vivek

If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[vivran22]

Hello @Pandadev ,

You may try the following:

Add Column > Extract > Last Characters

Enter the number of characters (in this case, 6).

Hope this helps.

Cheers!
Vivek

If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter