Splitting Columns by Numbers or Characters

K

Ken

New member
Joined
Apr 6, 2016
Messages
6
Reaction score
0
Points
0
Is there a way to split columns based on a number, with the number being any number from 0 to 9?
 
Please provide an example sheet ( no pics pse) of what you are trying to achieve. Thx
 
You could use a this function:

Splitter.SplitTextByAnyDelimiter( List.Transform({0..9},each Text.From(_)))(InputText)
 
bdarbonneau, thank you. Since I am inexperienced with Power Query, I have two questions:

1. How do I use this as a function? Do I just insert the code into the Advanced Editor?
2. Is the line of code you provided complete or do I have to insert anything into Text.From or replace InputText.

Also, does the same code apply for splitting a column with any letter, lowercase or uppercase?
 
Ken said:
bdarbonneau, thank you. Since I am inexperienced with Power Query, I have two questions:

1. How do I use this as a function? Do I just insert the code into the Advanced Editor?
2. Is the line of code you provided complete or do I have to insert anything into Text.From or replace InputText.

Also, does the same code apply for splitting a column with any letter, lowercase or uppercase?
Click to expand...

In the view tab of the query editor, make sure the box 'Formula bar' is ticked, so as to be able to diectly edit the code that PQ creates.
Start by using the UI: Split Column -> Split by delimiter -> colon. This will generate a step named 'Split Column by Delimiter'. Select it.
The code in the formula bar should look like:
Code: 
= Table.SplitColumn(#"Changed Type","ColumnA",Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv),{"ColumnA.1", "ColumnA.2"})

Then edit this line as to have the code below:
Code: 
= Table.SplitColumn(#"Changed Type","ColumnA",Splitter.SplitTextByAnyDelimiter( List.Transform({0..9},each Text.From(_))),{"ColumnA.1", "ColumnA.2"})
 
You must log in or register to reply here.
Back
Top