How to remove the last character in the column in power query excel?

0
votes

enter image description here

Hi all,

I want to remove the last character of the column, K and M. So that I'm able to add another column such that if the last character is M then the number x 1000000 else the number x 100000. Please give me some advice on what can I do, thank you.

The screenshot below is the step that already been applied to the Table. enter image description here

2
excelpowerquery
Will r-click (on the input), format cells, Number > Custom : 0 do the trick ? – p._phidot_
You mean doing in excel right? Is there any other method that allow me to do it in power query? – Elvis
yup (coz I see excel tag there). sorry, I don't have access to Power Query. I'm not sure if this will help : support.microsoft.com/en-us/office/… – p._phidot_

2 Answers

3
votes

To just remove the last character you can use Text.RemoveRange:

let
    Source = Excel.CurrentWorkbook(){[Name="Table14"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.RemoveRange([Query],Text.Length([Query])-1))
in
    #"Added Custom"

enter image description here

To convert strings ending in K or M to their real numbers, you can do things a bit more complex: (Note that I assumed K=1000 and M=1000000 to be consistent; other systems have M=1000 and MM=1000000. I am not aware of a system where K=100000 but you can easily edit the code if you need to)

let
    Source = Excel.CurrentWorkbook(){[Name="Table14"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Query", type text}}),
 
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 
        let 
            x = Text.End([Query],1),
            y = Number.From(Text.RemoveRange([Query],Text.Length([Query])-1)),
            result =    if x = "K" then y * 1000 
                   else if x = "M" then  y * 1000000

/*Note that code assumes that if string doesn't end with K or M
  it is a number. You may want to test specifically for that to avoid errors*/
  
                   else Number.From([Query])
        in 
            result, type number)
in
    #"Added Custom"

If you are having trouble entering the code, you can add it as a custom column to your existing code. Be sure to change the column identifier in the code to whatever the column name is in your code that references the table you show. Possibly [Query.Avg Volume]

eg:

enter image description here

enter image description here

0
votes

With the M-Code below the following input table

enter image description here

is expanded to this

enter image description here

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Avg", type text}}),
    #"Inserted First Characters" = Table.AddColumn(#"Changed Type", "Last Character", each Text.End([Avg], 1), type text),
    #"Added Custom" = Table.AddColumn(#"Inserted First Characters", "Start", each Text.Start([Avg], Text.Length([Avg])-1))
in
    #"Added Custom"