Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Split column into two columns

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

 
Need Help
  • mlsx4's avatar
    mlsx4
    2 years ago

    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

8 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    2 years ago

    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)))

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

     

     

  • mlsx4's avatar
    mlsx4
    Memorable Member
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        mlsx4
        Memorable Member
        2 years ago

        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)

  • JoeBarry's avatar
    JoeBarry
    Solution Sage
    2 years ago

    Hi Anonymous 

     

    Please provide a screenshot of the table before you unpivoted.

     

    thanks

    Joe

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

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

       

  • slorin's avatar
    slorin
    Super User
    2 years ago

    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