Using Excel Power Query to create new columns based on indentation of text

Question

What I am trying to achieve I think is best explained with some pictures.

I have the following example data set:

Raw Data

Using column1 I need to create two columns, the first will contain the text from the non indented rows and the other will contain the text from the indented rows, like so:

After Power Query

I need to transform column1 using Power Query to create 2 new columns. The first with the rows that are not indented, and the second with the rows that are indented.

Is this possible using Power Query.

Many thanks.

Answer 1

Column2 can be made with a custom column under Add Column with the following function:

= if Text.StartsWith([Column1]," ") then null else [Column1]

Then use the fill option under transform to fill the rows with the addresses

Column3 can be made with:

= if Text.StartsWith([Column1]," ") then [column1] else null