Forum Discussion
Split Columns
Hi All,
I am using Power Bi desktop.
I have a source file in excel. One o fthe column has similar information.
Column 1
Power Bi User Group Jul 6th
--
--
I want to separate date onto new column. I clicked on Transform. I duplicated current column 1 Copy. I splitted column , choose delimeter and space but it created 6 columns for each words. I want only two columns from above data as:
Column 1
Power Bi User Group
Column 2
Jul 6th
Please advise. Thanks
Hope this helps!
The below approach is one of the simplest approach ... (, may be better approach exists)...1. Duplicate the column and use this column
2. Split the column for the last word i..e, using Split using "Space" and "Right-most delimiter"
3. Split the first column from (2) for the last word
4. Merge the last two columns
Optional:
Rename the columns "Merged" and "Column1 - Copy.1.1" to your needs.
Roughly,
.... #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column1", "Column1 - Copy"), #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Column1 - Copy", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Column1 - Copy.1", "Column1 - Copy.2"}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column1 - Copy.1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Column1 - Copy.1.1", "Column1 - Copy.1.2"}), #"Merged Columns" = Table.CombineColumns(#"Split Column by Delimiter1",{"Column1 - Copy.1.2", "Column1 - Copy.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged") in #"Merged Columns"Regards
3 Replies
Hope this helps!
The below approach is one of the simplest approach ... (, may be better approach exists)...1. Duplicate the column and use this column
2. Split the column for the last word i..e, using Split using "Space" and "Right-most delimiter"
3. Split the first column from (2) for the last word
4. Merge the last two columns
Optional:
Rename the columns "Merged" and "Column1 - Copy.1.1" to your needs.
Roughly,
.... #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column1", "Column1 - Copy"), #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Column1 - Copy", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Column1 - Copy.1", "Column1 - Copy.2"}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column1 - Copy.1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Column1 - Copy.1.1", "Column1 - Copy.1.2"}), #"Merged Columns" = Table.CombineColumns(#"Split Column by Delimiter1",{"Column1 - Copy.1.2", "Column1 - Copy.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged") in #"Merged Columns"Regards
Can you use Text function as ashown below in Power Query?
Text.Range("Power Bi User Group Jul 6th", 21, 7)Are 21, 7 positions?
Every row has different words size.
