Forum Discussion
Helper IIISplit 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!
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
- mussaenda
Community Champion
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
vinaydavidThanks 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
And in your screenshot,
Before transformation, you havn't considered the other columns, where the data actually exists.
- HotChilli
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.
