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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors