CODEWITHSUNDEEP
excelpowerquery
Elvis
Elvis

Reputation: 425

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

enter image description here

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. What can I do?

The screenshot below is the step that already been applied to the table:

enter image description here

Upvotes: 0

Views: 10888

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

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

Upvotes: 5

Storax
Storax

Reputation: 12167

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"

Upvotes: 0

Related Questions