Splitting column individualy

[joshua1990]

Hi community!I have a column that contains a string in this format:

Type1 Type2 Product "in"Country City

String

Glas Tube A inFrance Lyon

Glas Tube B inSpain

Glas Tube C

Wood Chair A1 Spain Madrid

Now, based on the first word in the column I would like to apply an individual split of this column:

• If it starts with Glas then the columns should be splittet after each blank space and "in" should be seperated from Country
• If it starts with Wood, then the columns should be splittet after each blank space

How would you apply this kind of logic within power query?

[m_dekorte · Accepted solution]

Hi @joshua1990 

You could give this a go.

let
    ColNames = {"Type1", "Type2", "Product", "Country", "City"},
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs9JLFYIKU1KVXBUyMxzK0rMS05V8KnMz1OK1UGWdQLKBhckZqKLO4P54fn5KQrOGYmZRQqOhgpgdQq+iSlFmSlKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [String = _t]),
    SplitCol = Table.SplitColumn(Source, "String", Splitter.SplitTextByWhitespace() ),
    TrimStart = Table.TransformColumns(
        Table.RenameColumns( SplitCol, List.Zip( { Table.ColumnNames( SplitCol ), ColNames })),
        {{"Country", each Text.TrimStart(_, {"i", "n"}), type text }}
    )
in
    TrimStart

With this result