Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
joshua1990
Post Prodigy
Post Prodigy

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?

1 ACCEPTED SOLUTION
m_dekorte
Super User
Super User

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_0-1685780778811.png

 

View solution in original post

2 REPLIES 2
johnlhaase
Helper I
Helper I

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

m_dekorte
Super User
Super User

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_0-1685780778811.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors