Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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?

2 ACCEPTED SOLUTIONS
AlB
Super User
Super User

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 

 

SU18_powerbi_badge

 

View solution in original post

shaowu459
Resolver II
Resolver II

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

1.png 

View solution in original post

3 REPLIES 3
shaowu459
Resolver II
Resolver II

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

1.png 

AlB
Super User
Super User

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 

 

SU18_powerbi_badge

 

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 🙂



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors