Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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
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
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
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
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 🙂