Forum Discussion

joshua1990's avatar
joshua1990
Post Prodigy
3 years ago
Solved

Splitting column individualy

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?

Need Help
  • m_dekorte's avatar
    m_dekorte
    3 years ago

    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

     

2 Replies

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    3 years ago

    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

     

  • johnlhaase's avatar
    johnlhaase
    Helper I
    2 years ago

    Hello

     

    Here is the magic code

    = Table.AddColumn(#"Renamed Columns1", "New", each Text.Select([Name], {"a".."z"}))

    We can subsitute Upper and Lower case or number {0..9}.

    Thanks

    John Haase