Forum Discussion
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?
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
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
3 Replies
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
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 resI 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 🙂
