Split Text in a cell to adjacent columns using Delimiter

[vinaydavid]

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!

[mussaenda]

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

[vinaydavid]

And in your screenshot,

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

[HotChilli · 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.

[vinaydavid]

HotChilli.....You are the Man...

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!

[vinaydavid]

Thanks a lot HotChilli for your response.

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

Willl post you soon....

Regards

David

[vinaydavid]

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.