Split column into two columns

[Anonymous]

Hello all,

Im trying to split the data in a column "Value" into two collumns:

1. Value - will contains numbers only

2. Type - will contains all the text (Solid, Liquid, etc) in line with the corresponding Attribute

Table which I want to achieve should look like this:

Ive tried Split column option, but cant make it work.

It works when using Column from example, but when I add new items (attribute and value), it wont recognize and assign wrong value to Attribute - so no GO option for me.

Any idea is appreciated.

Thanks

[slorin]

Hi,

let
Source = Your_Source,
Custom1 = List.RemoveFirstN(Table.ColumnNames(Source),5),
Custom2 = List.RemoveFirstN(Record.ToList(Source{0}),5),
Data = Table.AddColumn(Source, "Data", each Table.FromColumns({ Custom1 , Custom2, List.RemoveFirstN(Record.ToList(_),5)})),
Custom3 = Table.RemoveColumns(Data,Custom1)
in
Custom3

then Expand

Stéphane

[JoeBarry]

Hi @Anonymous 

Please provide a screenshot of the table before you unpivoted.

thanks

Joe

[Anonymous]

@JoeBarry This is how data looks like before unpivoted (screenshot from excel)

[mlsx4 · Accepted solution]

Hi @Anonymous 

1. Trim Attribute column (just to avoid extra "space" characters)
2. Create a conditional column: if attribute = "Excalibur" then return "Solid" and so on...
3. Remove first 4 rows

[Anonymous]

Thanks @mlsx4,

I understand the point of making the conditional columns, but in the future I will have more new attributes  + values added and I want system to automatically recognize and link it with new value

[mlsx4]

And the point of create a duplication of the table after unpivot, filter (null values) for just keeping this: 

As a master table, and then combine values on the original one (having previously filtered out the null values)

[Anonymous]

Thanks @wdx223_Daniel 

Ive tried to add custom column with the command u have provided, but it doesnt aligned right attributes with right values (ie: Excalibur is linked with Liquid, but should be Solid)

see picture below

[wdx223_Daniel]

NewStep=Table.AddColumn(Table.Skip(#"Unpiovted Other Columns",4),"Type",Function.ScalarVector(Value.Type(each _) as any,(t)=>let a=#"Unpiovted Other Columns"[Value] in List.Repeat(List.FirstN(a,4),List.Count(a)/4-1)))