Split Columns

[sdhn]

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 

[sevenhills · Accepted solution]

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

[Shahfaisal]

Can you use Text function as ashown below in Power Query?

Text.Range("Power Bi User Group  Jul 6th", 21, 7)

[sdhn]

Are 21, 7 positions?

Every row has different words size.