How to change numbers like "13.5K" into "13500"?

[croberts21]

I have the free PBI Desktop v2.100.1182 (64 bit free version Dec 2021) on Windows 10.

I got some data from Gapminder.org for GDP per capita. There are about 200 rows and about 200 columns I have to convert. The original data from the original soure is "scaled" to end with "k" to indicate thousands, and "M" to indicate millions. This is not a PBI display issue, this is a data issue. Some values are a number like "683", which are fine. Some numbers use the "K" suffix to indicate thousands, like "13.5K". The numbers are actually a data type of text because of the "k" suffixes on some of the numbers. Not all numbers have a suffix like "k".

How do I change numbers like "13.5K" into a real number like "13500"? Is there a built-in function to do this in PBI?

Thank you!

p.s. My actual data sources contains the scaled numbers like "13.5k", so I need PBI to "unscale" them. 

Screenshot of the table view.

Some numbers in my datasource have no suffix, some have a suffix of "k" which means "thousands", some may have a suffix of "m" for millions. 

EDIT: I have to do this for 200 columns. Each column is a year which shows the population for that country. I tried selecting a column and replacing "k" with "000" and 000 but I PBI doesn't change any of the data.

[croberts21]

Thank you but this does not unscale the units in my original data source as the original data source contains the string "13.5k". Since that is a string, and not a number, PBI cannot unscale it using your method. Why the original data source is a string like that I don't know, but it's pretty effective at preventing people from using the data in any meaningful way.

[Samarth_18]

@croberts21 To replace "k" with thousand in a same column. You need to add one custom step in PQ as below:-

    #"Replaced Value" = Table.ReplaceValue(
   #"Changed Type",
  each [Column1],
  each if Text.EndsWith(Text.From([Column1]), "k")
  then Number.From(Text.Start([Column1], Text.Length([Column1]) - 1)) * 1000
  else [Column1],
  Replacer.ReplaceValue,
  {"Column1"}
)

[croberts21]

Oh! I just replace "k" with "000". I didn't think of that! I'll give it a try. I will have to replace "m" with "000000" also then change the column data type to a number.

Thank you! I'm glad I learned something though. And I took notes in my note application, which is a portable wiki in a single html file, called Tiddlywiki.

[Samarth_18]

@croberts21 You can add "m" condition as well in the above code. Let me know if it works 🙂

[croberts21]

This is not working, I get an error. Here are the steps I took: 

In the transfor window, I right clicked the last step, clicked "Insert Step After", where it inserted a step called "Custom1". 

Then I opened the advanced editor for the step Custom1 where I get this screen.

Then I replaced all of that with your formula and I get this: 

I click the Done button for that custom step and I get this error: 

Could this be a bug from our older version of PBI from December 2021?

[Samarth_18]

@croberts21 In the last line replace Custom1 with #"Changed Type".As below

in 

#"Changed Type"

[croberts21]

Hi, I am putting up the PBIX file and the supporting files here. 

https://gilsongraphics-my.sharepoint.com/:u:/p/croberts/EcwZIOnoKUlNqx7ahZtUAIcBBRpTEcY0NfDH2l92l3ez...

In Transform mode, the problem table is called population_total. It has 200 columns for the year and that's where some numbers have the "k" or "M" suffix.

[Samarth_18 · Accepted solution]

@croberts21 I have converted 3 columns and you can convert rest of the column by refering to it. Please refer the attached file.

let
    Source = Excel.Workbook(File.Contents("C:\population_total.xlsx"), null, true),
    population_total_Sheet = Source{[Item="population_total",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(population_total_Sheet,{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type any}, {"Column34", type any}, {"Column35", type any}, {"Column36", type any}, {"Column37", type any}, {"Column38", type any}, {"Column39", type any}, {"Column40", type any}, {"Column41", type any}, {"Column42", type any}, {"Column43", type any}, {"Column44", type any}, {"Column45", type any}, {"Column46", type any}, {"Column47", type any}, {"Column48", type any}, {"Column49", type any}, {"Column50", type any}, {"Column51", type any}, {"Column52", type any}, {"Column53", type any}, {"Column54", type any}, {"Column55", type any}, {"Column56", type any}, {"Column57", type any}, {"Column58", type any}, {"Column59", type any}, {"Column60", type any}, {"Column61", type any}, {"Column62", type any}, {"Column63", type any}, {"Column64", type any}, {"Column65", type any}, {"Column66", type any}, {"Column67", type any}, {"Column68", type any}, {"Column69", type any}, {"Column70", type any}, {"Column71", type any}, {"Column72", type any}, {"Column73", type any}, {"Column74", type any}, {"Column75", type any}, {"Column76", type any}, {"Column77", type any}, {"Column78", type any}, {"Column79", type any}, {"Column80", type any}, {"Column81", type any}, {"Column82", type any}, {"Column83", type any}, {"Column84", type any}, {"Column85", type any}, {"Column86", type any}, {"Column87", type any}, {"Column88", type any}, {"Column89", type any}, {"Column90", type any}, {"Column91", type any}, {"Column92", type any}, {"Column93", type any}, {"Column94", type any}, {"Column95", type any}, {"Column96", type any}, {"Column97", type any}, {"Column98", type any}, {"Column99", type any}, {"Column100", type any}, {"Column101", type any}, {"Column102", type any}, {"Column103", type any}, {"Column104", type any}, {"Column105", type any}, {"Column106", type any}, {"Column107", type any}, {"Column108", type any}, {"Column109", type any}, {"Column110", type any}, {"Column111", type any}, {"Column112", type any}, {"Column113", type any}, {"Column114", type any}, {"Column115", type any}, {"Column116", type any}, {"Column117", type any}, {"Column118", type any}, {"Column119", type any}, {"Column120", type any}, {"Column121", type any}, {"Column122", type any}, {"Column123", type any}, {"Column124", type any}, {"Column125", type any}, {"Column126", type any}, {"Column127", type any}, {"Column128", type any}, {"Column129", type any}, {"Column130", type any}, {"Column131", type any}, {"Column132", type any}, {"Column133", type any}, {"Column134", type any}, {"Column135", type any}, {"Column136", type any}, {"Column137", type any}, {"Column138", type any}, {"Column139", type any}, {"Column140", type any}, {"Column141", type any}, {"Column142", type any}, {"Column143", type any}, {"Column144", type any}, {"Column145", type any}, {"Column146", type any}, {"Column147", type any}, {"Column148", type any}, {"Column149", type any}, {"Column150", type any}, {"Column151", type any}, {"Column152", type any}, {"Column153", type any}, {"Column154", type any}, {"Column155", type any}, {"Column156", type any}, {"Column157", type any}, {"Column158", type any}, {"Column159", type any}, {"Column160", type any}, {"Column161", type any}, {"Column162", type any}, {"Column163", type any}, {"Column164", type any}, {"Column165", type any}, {"Column166", type any}, {"Column167", type any}, {"Column168", type any}, {"Column169", type any}, {"Column170", type any}, {"Column171", type any}, {"Column172", type any}, {"Column173", type any}, {"Column174", type any}, {"Column175", type any}, {"Column176", type any}, {"Column177", type any}, {"Column178", type any}, {"Column179", type any}, {"Column180", type any}, {"Column181", type any}, {"Column182", type any}, {"Column183", type any}, {"Column184", type any}, {"Column185", type any}, {"Column186", type any}, {"Column187", type any}, {"Column188", type any}, {"Column189", type any}, {"Column190", type any}, {"Column191", type any}, {"Column192", type any}, {"Column193", type any}, {"Column194", type any}, {"Column195", type any}, {"Column196", type any}, {"Column197", type any}, {"Column198", type any}, {"Column199", type any}, {"Column200", type any}, {"Column201", type any}, {"Column202", type any}, {"Column203", type any}, {"Column204", type any}, {"Column205", type any}, {"Column206", type any}, {"Column207", type any}, {"Column208", type any}, {"Column209", type any}, {"Column210", type any}, {"Column211", type any}, {"Column212", type any}, {"Column213", type any}, {"Column214", type any}, {"Column215", type any}, {"Column216", type any}, {"Column217", type any}, {"Column218", type any}, {"Column219", type any}, {"Column220", type any}, {"Column221", type any}, {"Column222", type any}, {"Column223", type any}, {"Column224", type any}, {"Column225", type any}, {"Column226", type any}, {"Column227", type any}, {"Column228", type any}, {"Column229", type any}, {"Column230", type any}, {"Column231", type any}, {"Column232", type any}, {"Column233", type any}, {"Column234", type any}, {"Column235", type any}, {"Column236", type any}, {"Column237", type any}, {"Column238", type any}, {"Column239", type any}, {"Column240", type any}, {"Column241", type any}, {"Column242", type any}, {"Column243", type any}, {"Column244", type any}, {"Column245", type any}, {"Column246", type any}, {"Column247", type any}, {"Column248", type any}, {"Column249", type any}, {"Column250", type any}, {"Column251", type any}, {"Column252", type any}, {"Column253", type any}, {"Column254", type any}, {"Column255", type any}, {"Column256", type any}, {"Column257", type any}, {"Column258", type any}, {"Column259", type any}, {"Column260", type any}, {"Column261", type any}, {"Column262", type any}, {"Column263", type any}, {"Column264", type any}, {"Column265", type any}, {"Column266", type any}, {"Column267", type any}, {"Column268", type any}, {"Column269", type any}, {"Column270", type any}, {"Column271", type any}, {"Column272", type any}, {"Column273", type any}, {"Column274", type any}, {"Column275", type any}, {"Column276", type any}, {"Column277", type any}, {"Column278", type any}, {"Column279", type any}, {"Column280", type any}, {"Column281", type any}, {"Column282", type any}, {"Column283", type any}, {"Column284", type any}, {"Column285", type any}, {"Column286", type any}, {"Column287", type any}, {"Column288", type any}, {"Column289", type any}, {"Column290", type any}, {"Column291", type any}, {"Column292", type any}, {"Column293", type any}, {"Column294", type any}, {"Column295", type any}, {"Column296", type any}, {"Column297", type any}, {"Column298", type any}, {"Column299", type any}, {"Column300", type any}, {"Column301", type any}, {"Column302", type any}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"country", type text}, {"1799", type any}, {"1800", type any}, {"1801", type any}, {"1802", type any}, {"1803", type any}, {"1804", type any}, {"1805", type any}, {"1806", type any}, {"1807", type any}, {"1808", type any}, {"1809", type any}, {"1810", type any}, {"1811", type any}, {"1812", type any}, {"1813", type any}, {"1814", type any}, {"1815", type any}, {"1816", type any}, {"1817", type any}, {"1818", type any}, {"1819", type any}, {"1820", type any}, {"1821", type any}, {"1822", type any}, {"1823", type any}, {"1824", type any}, {"1825", type any}, {"1826", type any}, {"1827", type any}, {"1828", type any}, {"1829", type any}, {"1830", type any}, {"1831", type any}, {"1832", type any}, {"1833", type any}, {"1834", type any}, {"1835", type any}, {"1836", type any}, {"1837", type any}, {"1838", type any}, {"1839", type any}, {"1840", type any}, {"1841", type any}, {"1842", type any}, {"1843", type any}, {"1844", type any}, {"1845", type any}, {"1846", type any}, {"1847", type any}, {"1848", type any}, {"1849", type any}, {"1850", type any}, {"1851", type any}, {"1852", type any}, {"1853", type any}, {"1854", type any}, {"1855", type any}, {"1856", type any}, {"1857", type any}, {"1858", type any}, {"1859", type any}, {"1860", type any}, {"1861", type any}, {"1862", type any}, {"1863", type any}, {"1864", type any}, {"1865", type any}, {"1866", type any}, {"1867", type any}, {"1868", type any}, {"1869", type any}, {"1870", type any}, {"1871", type any}, {"1872", type any}, {"1873", type any}, {"1874", type any}, {"1875", type any}, {"1876", type any}, {"1877", type any}, {"1878", type any}, {"1879", type any}, {"1880", type any}, {"1881", type any}, {"1882", type any}, {"1883", type any}, {"1884", type any}, {"1885", type any}, {"1886", type any}, {"1887", type any}, {"1888", type any}, {"1889", type any}, {"1890", type any}, {"1891", type any}, {"1892", type any}, {"1893", type any}, {"1894", type any}, {"1895", type any}, {"1896", type any}, {"1897", type any}, {"1898", type any}, {"1899", type any}, {"1900", type any}, {"1901", type any}, {"1902", type any}, {"1903", type any}, {"1904", type any}, {"1905", type any}, {"1906", type any}, {"1907", type any}, {"1908", type any}, {"1909", type any}, {"1910", type any}, {"1911", type any}, {"1912", type any}, {"1913", type any}, {"1914", type any}, {"1915", type any}, {"1916", type any}, {"1917", type any}, {"1918", type any}, {"1919", type any}, {"1920", type any}, {"1921", type any}, {"1922", type any}, {"1923", type any}, {"1924", type any}, {"1925", type any}, {"1926", type any}, {"1927", type any}, {"1928", type any}, {"1929", type any}, {"1930", type any}, {"1931", type any}, {"1932", type any}, {"1933", type any}, {"1934", type any}, {"1935", type any}, {"1936", type any}, {"1937", type any}, {"1938", type any}, {"1939", type any}, {"1940", type any}, {"1941", type any}, {"1942", type any}, {"1943", type any}, {"1944", type any}, {"1945", type any}, {"1946", type any}, {"1947", type any}, {"1948", type any}, {"1949", type any}, {"1950", type any}, {"1951", type any}, {"1952", type any}, {"1953", type any}, {"1954", type any}, {"1955", type any}, {"1956", type any}, {"1957", type any}, {"1958", type any}, {"1959", type any}, {"1960", type any}, {"1961", type any}, {"1962", type any}, {"1963", type any}, {"1964", type any}, {"1965", type any}, {"1966", type any}, {"1967", type any}, {"1968", type any}, {"1969", type any}, {"1970", type any}, {"1971", type any}, {"1972", type any}, {"1973", type any}, {"1974", type any}, {"1975", type any}, {"1976", type any}, {"1977", type any}, {"1978", type any}, {"1979", type any}, {"1980", type any}, {"1981", type any}, {"1982", type any}, {"1983", type any}, {"1984", type any}, {"1985", type any}, {"1986", type any}, {"1987", type any}, {"1988", type any}, {"1989", type any}, {"1990", type any}, {"1991", type any}, {"1992", type any}, {"1993", type any}, {"1994", type any}, {"1995", type any}, {"1996", type any}, {"1997", type any}, {"1998", type any}, {"1999", type any}, {"2000", type any}, {"2001", type any}, {"2002", type any}, {"2003", type any}, {"2004", type any}, {"2005", type any}, {"2006", type any}, {"2007", type any}, {"2008", type any}, {"2009", type any}, {"2010", type any}, {"2011", type any}, {"2012", type any}, {"2013", type any}, {"2014", type any}, {"2015", type any}, {"2016", type any}, {"2017", type any}, {"2018", type any}, {"2019", type any}, {"2020", type any}, {"2021", type any}, {"2022", type any}, {"2023", type any}, {"2024", type any}, {"2025", type any}, {"2026", type any}, {"2027", type any}, {"2028", type any}, {"2029", type any}, {"2030", type any}, {"2031", type any}, {"2032", type any}, {"2033", type any}, {"2034", type any}, {"2035", type any}, {"2036", type any}, {"2037", type any}, {"2038", type any}, {"2039", type any}, {"2040", type any}, {"2041", type any}, {"2042", type any}, {"2043", type any}, {"2044", type any}, {"2045", type any}, {"2046", type any}, {"2047", type any}, {"2048", type any}, {"2049", type any}, {"2050", type any}, {"2051", type any}, {"2052", type any}, {"2053", type any}, {"2054", type any}, {"2055", type any}, {"2056", type any}, {"2057", type any}, {"2058", type any}, {"2059", type any}, {"2060", type any}, {"2061", type any}, {"2062", type any}, {"2063", type any}, {"2064", type any}, {"2065", type any}, {"2066", type any}, {"2067", type any}, {"2068", type any}, {"2069", type any}, {"2070", type any}, {"2071", type any}, {"2072", type any}, {"2073", type any}, {"2074", type any}, {"2075", type any}, {"2076", type any}, {"2077", type any}, {"2078", type any}, {"2079", type any}, {"2080", type any}, {"2081", type any}, {"2082", type any}, {"2083", type any}, {"2084", type any}, {"2085", type any}, {"2086", type any}, {"2087", type any}, {"2088", type any}, {"2089", type any}, {"2090", type any}, {"2091", type any}, {"2092", type any}, {"2093", type any}, {"2094", type any}, {"2095", type any}, {"2096", type any}, {"2097", type any}, {"2098", type any}, {"2099", type any}}),
    #"Replaced Value1799" = Table.ReplaceValue(
   #"Changed Type1",
  each [1799],
  each if Text.EndsWith(Text.From([1799]), "k")
  then Number.From(Text.Start([1799], Text.Length([1799]) - 1)) * 1000
  else if Text.EndsWith(Text.From([1799]), "M")
  then Number.From(Text.Start([1799], Text.Length([1799]) - 1)) * 1000000
  else [1799],
  Replacer.ReplaceValue,
  {"1799"}
),
 #"Replaced Value1800" = Table.ReplaceValue(
   #"Replaced Value1799",
  each [1800],
  each if Text.EndsWith(Text.From([1800]), "k")
  then Number.From(Text.Start([1800], Text.Length([1800]) - 1)) * 1000
  else if Text.EndsWith(Text.From([1800]), "M")
  then Number.From(Text.Start([1800], Text.Length([1800]) - 1)) * 1000000
  else [1800],
  Replacer.ReplaceValue,
  {"1800"}
),
 #"Replaced Value1801" = Table.ReplaceValue(
   #"Replaced Value1800",
  each [1801],
  each if Text.EndsWith(Text.From([1801]), "k")
  then Number.From(Text.Start([1801], Text.Length([1801]) - 1)) * 1000
  else if Text.EndsWith(Text.From([1801]), "M")
  then Number.From(Text.Start([1801], Text.Length([1801]) - 1)) * 1000000
  else [1801],
  Replacer.ReplaceValue,
  {"1801"}
------------
----------
----------
#"Replaced ValueN" = Table.ReplaceValue(
#"previous replaced valueN",
  each [N],
  each if Text.EndsWith(Text.From([N]), "k")
  then Number.From(Text.Start([N], Text.Length([N]) - 1)) * 1000
  else if Text.EndsWith(Text.From([N]), "M")
  then Number.From(Text.Start([N], Text.Length([N]) - 1)) * 1000000
  else [N],
  Replacer.ReplaceValue,
  {"N"}
)
in
   #"Replaced ValueN"

//N refers to column you wanted to convert

[croberts21]

FYI, I was unable to get this to work and I have to move on to other problems. 

[croberts21]

I don't understand. Do you mean this would be the new code? 

   #"Replaced Value" = Table.ReplaceValue(
   #"Changed Type",
  each [Column1],
  each if Text.EndsWith(Text.From([Column1]), "k")
  then Number.From(Text.Start([Column1], Text.Length([Column1]) - 1)) * 1000
  else [Column1],
  Replacer.ReplaceValue,
  {"Changed Type"}
)

The last line is now: {"Changed Type"} 

[Samarth_18]

@croberts21 it should be like this:-

let
 Source = 
........
..........
..........
{"Column1"}
)
in
#"Replaced Value"

[Samarth_18]

Hi @croberts21 ,

Set display units as None as below:-

Regards,

Samarth