cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vinaydavid Frequent Visitor
Frequent Visitor

Split Text in a cell to adjacent columns using Delimiter

Hi folks,

 

I have just started learning PBI (days). Help me find a way on how to go about solving the below situation. (I have tried split function, but not sure if I am missing something to achieve the desired result)

 

Scenerio:

Col1                         | Col2 | Col 3 | Col 4 | Col 5 | Col 6

abc / def / ghi / jkl    null    null      null     25         40

xyz                             text    text     text     50         70

 

Here, I want to get the text from 1st Row to the adjacent  columns, and quite obviously using delimiter ' / '.

 

Result:

Col1                         | Col2 | Col 3 | Col 4 | Col 5 | Col 6

abc                             def      ghi      jkl       25         40

xyz                              text    text     text     50         70

 

Appreciate your inputs!

Cheers!

1 ACCEPTED SOLUTION

Accepted Solutions
HotChilli Senior Member
Senior Member

Re: Split Text in a cell to adjacent columns using Delimiter

After splitting the column, 3 new columns will be created.

You can then merge each of the 3 columns with the original columns that need to go together.

(ctrl click the two columns and choose 'Merge columns' from the Transform menu)

You'll do this 3 times - once for 'def' and it's 'partner', once for 'ghi' + partner, once for 'jkl' + partner

For example, merge the column that has 'def' with the original 'Col2'.

That will give a column with

'defnull'

text

You then need to remove the 'null' part from the column.  This can be tricky but if you right-click the column, use 'replace values'. Type null, leave the other box empty.

It will look like this hasn't worked but if you edit the line in the advanced editor, to put quotes round null.

It'll look something like this

= Table.ReplaceValue(#"Trimmed Text","null","",Replacer.ReplaceText,{"Merged.2"})

Do this for each column as required.

 

6 REPLIES 6
mussaenda Member
Member

Re: Split Text in a cell to adjacent columns using Delimiter

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVtBXSElNA5LpGZlAMis7R0lHycgUSJgYKMXqRCtVVFYpAHmmBkDCHCgUCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}}),
#"Transposed Table" = Table.Transpose(#"Changed Type1"),
#"Changed Type2" = Table.TransformColumnTypes(#"Transposed Table",{{"Column2", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type2",null,"text",Replacer.ReplaceValue,{"Column2"}),
#"Transposed Table1" = Table.Transpose(#"Replaced Value"),
#"Changed Type3" = Table.TransformColumnTypes(#"Transposed Table1",{{"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}})
in
#"Changed Type3"

Hi, Please see the Queries above with the screenshot below

 

 

2019_05_30_18_29_54_Untitled_Power_Query_Editor.png

vinaydavid Frequent Visitor
Frequent Visitor

Re: Split Text in a cell to adjacent columns using Delimiter

Thanks mussaenda

 

As I look at your query, looks like you have used replace emply values 'null' with 'text'.

But for my scenerio, the text with delimiter is the Header. (Only this cell has header clubbed as a single text)

 

Below the header, all valid data is available.......nothing has to be changed here.

 

 

vinaydavid Frequent Visitor
Frequent Visitor

Re: Split Text in a cell to adjacent columns using Delimiter

And in your screenshot,

Before transformation, you havn't considered the other columns, where the data actually exists.

 

HotChilli Senior Member
Senior Member

Re: Split Text in a cell to adjacent columns using Delimiter

After splitting the column, 3 new columns will be created.

You can then merge each of the 3 columns with the original columns that need to go together.

(ctrl click the two columns and choose 'Merge columns' from the Transform menu)

You'll do this 3 times - once for 'def' and it's 'partner', once for 'ghi' + partner, once for 'jkl' + partner

For example, merge the column that has 'def' with the original 'Col2'.

That will give a column with

'defnull'

text

You then need to remove the 'null' part from the column.  This can be tricky but if you right-click the column, use 'replace values'. Type null, leave the other box empty.

It will look like this hasn't worked but if you edit the line in the advanced editor, to put quotes round null.

It'll look something like this

= Table.ReplaceValue(#"Trimmed Text","null","",Replacer.ReplaceText,{"Merged.2"})

Do this for each column as required.

 

vinaydavid Frequent Visitor
Frequent Visitor

Re: Split Text in a cell to adjacent columns using Delimiter

Thanks a lot HotChilli for your response.

Let me try straight away and see what I end up with.

Willl post you soon....Smiley Happy

 

Regards

David

 

vinaydavid Frequent Visitor
Frequent Visitor

Re: Split Text in a cell to adjacent columns using Delimiter

HotChilli.....You are the Man...Smiley Wink

You made my day.....Toiled all night (part of struggle for beginners).

 

Just to add, the need of Replacing values was needed.

The merge worked perfectly as I needed.

Marked as solution...

Cheers!