Forum Discussion
Remove first 2 characters from a column value
From PQE, you can perform the below for having last 2 digits of Number column:
1. Duplicate the Number column2. Perform below operations from Home ->Transform section at top:
Split Column >> By Number of Characters
3. Modify with below:
Number of Characters = 2
Split = Once, as far right as possible
4. Remove unwanted split column and rename new column
Alternatively, you can use below formula in Advanced Editor:
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Number", "Duplicated Number"),
#"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Duplicated Column", {{"Duplicated Number", type text}}, "en-IN"), "Duplicated Number", Splitter.SplitTextByPositions({0, 2}, true), {"Duplicated Number", "New Number"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Position",{"Duplicated Number"})Don't forget to give thumbs up and accept this as a solution if it helped you !!!
I might add that my first post was not completely correct.
My data doesn't contain text, only numbers.
So I need to find a PQE function with which I can show the last 2 digits of a number
From PQE, you can perform the below for having last 2 digits of Number column:
1. Duplicate the Number column
2. Perform below operations from Home ->Transform section at top:
Split Column >> By Number of Characters
3. Modify with below:
Number of Characters = 2
Split = Once, as far right as possible
4. Remove unwanted split column and rename new column
Alternatively, you can use below formula in Advanced Editor:
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Number", "Duplicated Number"),
#"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Duplicated Column", {{"Duplicated Number", type text}}, "en-IN"), "Duplicated Number", Splitter.SplitTextByPositions({0, 2}, true), {"Duplicated Number", "New Number"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Position",{"Duplicated Number"})
Don't forget to give thumbs up and accept this as a solution if it helped you !!!
This worked, thanks.
But isn't there a simple statement that can do this with numbers, just like the statements that are there for text?
Namoh to be fair, you did give us an alphanumeric example. Try the following.
= Text.Range(Number.ToText([Old]),2)I know this is an old post but a mathematical solution to getting the last two digits might be to rounddown and subtract from the original value:
'Table'[Old]-Number.RoundDown('Table'[Old],-2)
