Forum Discussion
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.
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 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
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.
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.
Thanks Marcel.