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

[Anonymous]

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?

[AlB · Accepted solution]

Hi @Anonymous 

If you can use the code column to sort the data as you have in the first table, you could do this. Paste the following in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdDNCsIwDMDxVxk5T1m6tbpH8BnKDulaUPDjUvT1LUXiKclphX/a/UiMgDDCo9CzfS7D/fUuw62283RcPGxjBCf1cOp9Fu+vvS9Sd9i7F98PvQs+SlnjFc22Z01GGouShkKNhBoJNZG6KGTQpx64X6n+F+2YJY34wDhpBGcmSiPnlaHijyaD+1uwjKViUHfDmQxkLgYRDSEaQDSAaABb374=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CODE = _t, ATTR = _t, TEXT = _t, VALUE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CODE", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"CODE", "ATTR", "TEXT"}, {{"Count", each Text.Combine([VALUE],",")}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Count", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Count.1", "Count.2", "Count.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ATTR", type text}, {"TEXT", type text}, {"Count.1", Int64.Type}, {"Count.2", type text}, {"Count.3", Int64.Type}})
in
    #"Changed Type1"

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[shaowu459 · Accepted solution]

Hi, @Anonymous 

Copy and paste below code into the advanced editor, remember change "Table1" to the source table name.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    res = Table.SplitColumn(Table.Group(Source, {"CODE", "ATTR", "TEXT"},{"n",each _[VALUE]}),"n",each _,{"VALUE","LETTERS","POSITION"})
in
    res

 

[AlB · Accepted solution]

Hi @Anonymous 

If you can use the code column to sort the data as you have in the first table, you could do this. Paste the following in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdDNCsIwDMDxVxk5T1m6tbpH8BnKDulaUPDjUvT1LUXiKclphX/a/UiMgDDCo9CzfS7D/fUuw62283RcPGxjBCf1cOp9Fu+vvS9Sd9i7F98PvQs+SlnjFc22Z01GGouShkKNhBoJNZG6KGTQpx64X6n+F+2YJY34wDhpBGcmSiPnlaHijyaD+1uwjKViUHfDmQxkLgYRDSEaQDSAaABb374=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CODE = _t, ATTR = _t, TEXT = _t, VALUE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CODE", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"CODE", "ATTR", "TEXT"}, {{"Count", each Text.Combine([VALUE],",")}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Count", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Count.1", "Count.2", "Count.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ATTR", type text}, {"TEXT", type text}, {"Count.1", Int64.Type}, {"Count.2", type text}, {"Count.3", Int64.Type}})
in
    #"Changed Type1"

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[MattAllington]

I suggest you add a custom column like

= try Number.From([Value]) otherwise null

this should add a column with numbers or null. Call the column "Custom". You can then use this column to add another column

= if [Custom] = null then "Text" else "Number"

call it Custom2

you can then delete custom and pivot Custom2. 

i'm sure there is a way to do it with less steps, but I'm not at my PC to test it 🙂