How to split column in Power Query by the first space?

0
votes

I am using Power Query and have a column called LandArea; example data is "123.5 sq mi". It is of data type text. I want to remove the "sq mi" part so I just have the number value, 123.5. I tried the Replace function to replace "sq mi" with blank but that doesn't work because it looks at the entire text. So I tried to use Split where I split it on the space and it generated this formula below, and it did create a new column, but with null for all values. The original column still had "123.5 sq mi".

Table.SplitColumn(#"Reordered Columns1","LandArea",Splitter.SplitTextByDelimiter(" ", QuoteStyle.None),{"LandArea.1", "LandArea.2"})

When just splitting at the left-most delimiter:

Table.SplitColumn(#"Reordered Columns1","LandArea",Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, false),{"LandArea.1", "LandArea.2"})

I have also tried changing to QuoteStyle.Csv. Any idea how I can get this to work?

2
powerbipowerquery

2 Answers

0
votes

Use this to create a custom column:

= Table.AddColumn(
  #"Reordered Columns1",
  "NewColumn",
  each Text.Start([LandArea],Text.PositionOf([LandArea]," "))
)

UPDATE: Every one appears to have "sq mi"

= Table.AddColumn(#"Changed Type", "Custom", each Text.Replace([LandArea]," sq mi",""),type number)

Hope it helps.

0
votes

This is what I ended up using:

Table.AddColumn(#"Reordered Columns1", "LandArea2", 
    each Text.Start([LandArea], Text.PositionOf([LandArea], "sq")-1))

I avoided trying to find whitespace.