Remove last 4 numbers of a row

[Reimer]

I would like to remove the last 4 numbers of a column. Does anyone know how to do this?

[Prinshika3]

Select a blank cell, enter formula =RIGHT(A2,4) into the Formula Bar, and then press the Enter key for removing the last 4 digits of numbers or more!

[amitchandak]

@Reimer , you want to remove rows of only in the column. You have few options under data transformation

refer: https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data

[Pragati11]

Hi @Reimer ,

Does this removing last 4 numbers from a row is conditional based or you just want to eliminate last 4 rows from your dataset?

You can use something as below using M query in Query Editor in Power BI to remove rows from data:

https://docs.microsoft.com/en-us/powerquery-m/table-removerows

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[Reimer]

I think this is just not quite it. Let me try to explain a bit better.

A column in Power BI is a value like "12345670000" But sometimes it is "123456780000" I just need to remove the 0000.

I tried using splitting the column. This nearly worked :-,), but the problem is that sometimes there are 7 number, sometimes 8

[Pragati11]

Hi @Reimer ,

You can try transforming your column in Query Editor. Check for the following option and remove last 4 characters from your column ("0000"):

Let me know if this is what you are looking for.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[Reimer]

Almost! Useing this will delete anything after a specified amound of numbers, but I am looking for a way to just delete the last 4. because sometimes I need to keep 6 or 7 numbers

[V-pazhen-msft · Accepted solution]

@Reimer 

You may create a new column to remove the last 4 strings, remember change the new column from Text type to Wholenumber.

Column2 = LEFT('Table'[Column1],LEN([Column1])-4)

BTW, if you create a column using M query from @Pragati11 reply, you need to change the original colum type to Text first, then create that custom column.

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pragati11]

Hi @Reimer ,

You can use a M query code to achive this:

Text.Start([column];Text.Length([column])-2)

Replace [column] with your column-name. This remove last 2 characters, so replace 2 with 4 in your case.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati