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

Question

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?

alejandro zuleta · Accepted Answer · 2017-02-10 16:36:19Z

0

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.

edited Feb 10, 2017 at 16:36

answered Feb 10, 2017 at 16:17

alejandro zuleta

14.1k4 gold badges34 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Kelly Over a year ago

This did not work. I also tried changing it to use Text.Range instead. It creates a new column with "Error" in it. I am still trying to figure out the right formula for adding a new column, but also still feel like Split should work?

alejandro zuleta Over a year ago

@Kelly, What is the error you get? Do all your values have at least one white space?

Kelly Over a year ago

good question, they do all have whitespace. Every one appears to have "sq mi" in it, there are no nulls or blanks in the original data.

Kelly Over a year ago

Error: Expression.Error: The 'count' argument is out of range. Details: -1

Kelly Over a year ago

so I changed the formula to hardcode the Text.PositionOf result to 5 and it gave me values. So somewhere in there it's calculating a value that is out of range for Text.PositionOf...

|

Kelly · Accepted Answer · 2017-02-10 16:44:31Z

0

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.

answered Feb 10, 2017 at 16:44

Kelly

1,0142 gold badges20 silver badges36 bronze badges

1 Comment

ninMonkey Over a year ago

Position of can be configured to return only one match, like ` = Text.PositionOf( text, substring, Occurrence.First )` -- Occurrence.Last is useful as well

Collectives™ on Stack Overflow

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

2 Answers 2

8 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related