split 1 column into more columns

[JP-Ronse]

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:

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.

[v-huizhn-msft]

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

[MarcelBeug]

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:

[JP-Ronse]

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

[MarcelBeug]

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"

[JP-Ronse]

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 · Accepted solution]

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.

[JP-Ronse]

Thanks Marcel.

[JP-Ronse]

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