Add sections of a table column as new Columns - Power Query

Question

I am having the following Unpivoted table that contains Stat-tested % values and their Stat-letters and Stat-Letters position indicators on separate rows.

----------------------------------------
CODE  |  ATTR      |  TEXT     |  VALUE
----------------------------------------
1       mean        I love it     0.45
2       mean        I love it     0.67
3       mean        I love it     0.49
4       mean        I love it     0.21
5       mean        I love it     0.66
1       mean        I love it     abd
2       mean        I love it     e
3       mean        I love it     cd
4       mean        I love it     a
5       mean        I love it     ab
1       mean        I love it     1
2       mean        I love it     1
3       mean        I love it     1
4       mean        I love it     1
5       mean        I love it     1
1       wt-mean     I hate it     0.22
2       wt-mean     I hate it     0.56
3       wt-mean     I hate it     0.13
4       wt-mean     I hate it     0.89
5       wt-mean     I hate it     0.50
1       wt-mean     I hate it     ab
2       wt-mean     I hate it     ae
3       wt-mean     I hate it     c
4       wt-mean     I hate it     b
5       wt-mean     I hate it     de
1       wt-mean     I hate it     1
2       wt-mean     I hate it     1
3       wt-mean     I hate it     1
4       wt-mean     I hate it     1
5       wt-mean     I hate it     1

I want to group on the CODE column and add the Stat-tested Letters and position indicators as separate columns like below:

----------------------------------------------------------------
CODE  |  ATTR      |  TEXT     |  VALUE     LETTERS     POSITION
----------------------------------------------------------------
1       mean        I love it     0.45      abd         1
2       mean        I love it     0.67      e           1
3       mean        I love it     0.49      cd          1
4       mean        I love it     0.21      a           1
5       mean        I love it     0.66      ab          1
1       wt-mean     I hate it     0.22      ab          1
2       wt-mean     I hate it     0.56      ae          1
3       wt-mean     I hate it     0.13      c           1
4       wt-mean     I hate it     0.89      b           1
5       wt-mean     I hate it     0.50      de          1

The problem i am encountering while grouping the data on Value column, is that the column has mixed data types (text, number). How to split these into individual columns as shown below?

Answer 1

You can insert new custom-columns for this, check with try if the value is a number.

My Source is Tabelle1

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    Change_Type = Table.TransformColumnTypes(Quelle,{{"CODE", Int64.Type}, {"ATTR", type text}, {"TEXT", type text}, {"VALUE", type any}}),
    Custom_Number = Table.AddColumn(Change_Type, "Number", each if try Number.From([VALUE]) < 1 otherwise null = true then [VALUE] else null),
    Custom_Letters = Table.AddColumn(Custom_Number, "Letters", each if (try Number.From([VALUE]) >= 1 otherwise null) = null then [VALUE] else null),
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(Custom_Letters, "POSITION", each if [Number] = null and [Letters]= null then [VALUE] else null),
    Grouped_Rows = Table.Group(#"Hinzugefügte benutzerdefinierte Spalte", {"CODE", "ATTR", "TEXT"}, {{"VALUE", each List.Max([Number]), type nullable number}, {"LETTERS", each List.Max([Letters]), type nullable text}, {"POSITION", each List.Max([POSITION]), type nullable number}})
in
    Grouped_Rows