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 Tu...
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

     

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