Forum Discussion
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:
- Rank
- Title
- Title
- Perfomer
- Previous rank or new entry in this edition.
- 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:
| rank | 945 |
| title | Especially for you |
| title | Especially for you |
| perfomer | KYLIE MINOGUE & JASON DONOVAN |
| new or previous rank | nieuw binnengekomen in deze editie. |
| rank | 946 |
| title | Zo mooi zo blond en zo alleen |
| title | Zo mooi zo blond en zo alleen |
| perfomer | Jimmy Frey |
| new or previous rank | vorige editie op positie 431. |
| empty row | |
| comment | In 2002 wordt het lied Zo mooi, zo blond en zo alleen door Radio 2 en Sabam opgenomen in de Eregalerij. |
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.
8 Replies
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:
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:
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
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"
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
