cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JP-Ronse Regular Visitor
Regular Visitor

split 1 column into more columns

Hi All,

 

Allow me another question.

 

The Flemish radio is now playing the top 1000 classics and I copied the playlist into Excel. My question is just for fun, not in a really need to get a solution, just exploring the power of PQ.

 

The data is in 1 column in following sequence:

  1. Rank
  2. Title
  3. Title
  4. Perfomer
  5. Previous rank or new entry in this edition.
  6. Sometimes a blank cell, sometimes a comment.

 

You can find some sample data below. Sorry, it is all in Dutch.

 

What I would like to have as result:

 

Rank, Title, Perfomer, new or previous rank (4 columns)

 

Using Power Query add-in for Excel

 

TIA & kind regards,

 

JP-Ronse

 

P.S. My Excel knowledge is above average and I am a very active member as JP Ronse on the Dutch and English Excel forum. If I was in a really need to get this solved, I could do it in Excel, but as said this is just exploring.

 

Sample data:

 

rank945
titleEspecially for you
titleEspecially for you
perfomerKYLIE MINOGUE & JASON DONOVAN
new or previous ranknieuw binnengekomen in deze editie.
rank946
titleZo mooi zo blond en zo alleen
titleZo mooi zo blond en zo alleen
perfomerJimmy Frey
new or previous rankvorige editie op positie 431.
empty row 
commentIn 2002 wordt het lied Zo mooi, zo blond en zo alleen door Radio 2 en Sabam opgenomen in de Eregalerij.
1 ACCEPTED SOLUTION

Accepted Solutions
MarcelBeug Super Contributor
Super Contributor

Re: split 1 column into more columns

It can be done with the formula bar or the Advanced Editor.

 

Typically I would choose some text colum and choose Format - Trim in the Transform menu, just to create some base code.

Sometimes I even create some dummy text colum first and use this column to create the base code.

 

Then I adjust the code to the required transformation.

 

If applicable, the step in which the dummy text column was created, can be removed.

 

Specializing in Power Query Formula Language (M)

View solution in original post

8 REPLIES 8
MarcelBeug Super Contributor
Super Contributor

Re: split 1 column into more columns

It depends on the exact specifications of the data.

A link to the website would have helped.

 

Anyhow, with your example data, it can be done with the following steps.

Notice that each group must have 4 rows (after filtering) for this solution to work.

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type"),
    #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each ([Column1] <> "comment" and [Column1] <> "empty row")),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 4), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Column1]), "Column1", "Column2"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

 

This is how it was created:

Specializing in Power Query Formula Language (M)
Microsoft v-huizhn-msft
Microsoft

Re: split 1 column into more columns

Hi @JP-Ronse,

After research and review the solution @MarcelBeug posted, it's perfect. Please try and test using it. And please mark the right reply as answer if your issue has been resolved, otherwise, please feel free to ask if you have any other issue.

Best Regards,
Angelia

JP-Ronse Regular Visitor
Regular Visitor

Re: split 1 column into more columns

Hi Marcel,

 

The very best wishes for 2018.

 

You are near but it is my fault because I didn't explain enough and that is the reason why I can't pivot it.

 

Column1 (rank, title...) does not exist - I had to mention it - I added it to explain the meaning of each row.So you only habe column 2 to work on.

 

Also the step Remove Duplicates will not only remove the duplicate title but also the rank in case it is a new item ("nieuw binnengekomen in deze editie") and I want to keep this.

 

What I have so far:

 

2018-01-01 13_28_02-Table1 - Query Editor.png

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Top_1000", type any}}),
    #"Removed Blank Rows" = Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Inserted Text Length" = Table.AddColumn(#"Removed Blank Rows", "Length", each Text.Length(Text.From([Top_1000], "en-GB")), type number),
    #"Filtered Rows" = Table.SelectRows(#"Inserted Text Length", each [Length] <= 35),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 5), Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Integer-Divided Column",{"Length"})
in
    #"Removed Columns"

Is it possible to add a column with the repeating value (rank, title1, title2, performer, previous edition)?

 

Kind regards,

 

JP-Ronse

MarcelBeug Super Contributor
Super Contributor

Re: split 1 column into more columns

Yes, it can. In the query below I remove the first column and then reconstuct it. Prerequisite is that each group of data starts with a number and the other data are not numbers.

 

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column1"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Group", each if [Column2] is number then [Index] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Group"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "FieldLabel", each [Index] - [Group]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each [FieldLabel] < 5),
    LabelsToText = Table.TransformColumns(#"Filtered Rows", {{"FieldLabel", each {"Rank","Title1","Title2","Performer","Previous edition"}{_}, type text}}),
    #"Removed Columns1" = Table.RemoveColumns(LabelsToText,{"Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[FieldLabel]), "FieldLabel", "Column2"),
    #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Group"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns2",{{"Rank", Int64.Type}, {"Title1", type text}, {"Title2", type text}, {"Performer", type text}, {"Previous edition", type text}})
in
    #"Changed Type1"

 

 

Specializing in Power Query Formula Language (M)
JP-Ronse Regular Visitor
Regular Visitor

Re: split 1 column into more columns

Hi Marcel,

 

Gives me some stuff to think about in the coming days. Now trying to translate your query into my model. May take me some days as I have professional duties as from tomorrow but I'll return.

 

My model is slightly different and I really want to understand the steps to execute.

 

Kind regards,

 

JP-Ronse

JP-Ronse Regular Visitor
Regular Visitor

Re: split 1 column into more columns

Hi Marcel,

 

I was able to reconstruct the different steps by using the GUI but got stuck on:

 

LabelsToText = Table.TransformColumns(#"Filtered Rows", {{"FieldLabel", each {"Rank","Title1","Title2","Performer","Previous edition"}{_}, type text}}),

 

Can you explain how to with the GUI or is this only possible in the advanced editor?

 

 

Kind regards,

 

JP-Ronse.

MarcelBeug Super Contributor
Super Contributor

Re: split 1 column into more columns

It can be done with the formula bar or the Advanced Editor.

 

Typically I would choose some text colum and choose Format - Trim in the Transform menu, just to create some base code.

Sometimes I even create some dummy text colum first and use this column to create the base code.

 

Then I adjust the code to the required transformation.

 

If applicable, the step in which the dummy text column was created, can be removed.

 

Specializing in Power Query Formula Language (M)

View solution in original post

JP-Ronse Regular Visitor
Regular Visitor

Re: split 1 column into more columns

Thanks Marcel.

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors