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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
vinaydavid
Helper III
Helper III

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

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.

 

View solution in original post

6 REPLIES 6
mussaenda
Super User
Super User

 

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

And in your screenshot,

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

 

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.

 

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!

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

 

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.

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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