Forum Discussion
Solved
Grouping column text values in Power Query
Hi, I hope you are well. I have the above dataset and I need to find a clean and simple solution to group the categories from Characteristic 1 - Characteristic 7 in Power Query (not DAX or ...
Hi KAURM
The source was done by Enter Data, so it was generated by Power Query. I have two queries, one is called rawData which is your sample data, the other is dimTable which is the output. To make things easier, you right click to open a blank query, and go to Advanced Editor, paste the whole code. Then you can see the steps which you can apply to your original data
To make it easier, I put them in one query, if you still can't get it, pm your email, I will send you the sample .pbix file
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xVXbSsNAEP2V0GfFuezM7j4WBN8U1LfSh1TXGqgJtFX0b/wWv8zpTcUmNItCQx6ykz3ZM2fOTEajAZ4iA7qoEgYng8umTsXVQ3H7mIrhpHlJFvt9j082KO8ichQLDaerfR/v59WinFSzavm2Xl6k+j7NfzwW81QuFtW0fkr1ch2/Lu820Os0q6ZVUxdlfX/WzIuJrdPD+tVNen0uZ0UzrwxVLm3TloJHp0Sej0gBmQJIoNBFoUU5BgI1mLNQF90WGKEXRRbZh3Uyb/sMR7uIFCx2OPeWLwiyiyysmYbBQD5o3OPfoRKbRpavzzuEzJMknEfNgSo7YZTMwzgGBV1RbHFIJwyiZ9Xoj2hbik6s5Ts7p60gwh7JdMqTNggCQyD4n2zbdd5lRU6BY55lKAg79Y7+1ldis8gH9tDT3wRRkFAzrYoEAZ2jVY796H2XgiFansz4oxSHfbs7F8H6iqm7jD3ofA1NtToR91NKIERla2zotx9RHXggypdIMVpzkj9ma5ovBGg9JjPZI9og2/4h+kPHnw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LocationID = _t, #"Characteristic 1" = _t, #"Characteristic 2" = _t, #"Characteristic 3" = _t, #"Characteristic 4" = _t, #"Characteristic 5" = _t, #"Characteristic 6" = _t, #"Characteristic 7" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"LocationID", type text}, {"Characteristic 1", type text}, {"Characteristic 2", type text}, {"Characteristic 3", type text}, {"Characteristic 4", type text}, {"Characteristic 5", type text}, {"Characteristic 6", type text}, {"Characteristic 7", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"LocationID"}), #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Characteristic 1", Text.Trim, type text}, {"Characteristic 2", Text.Trim, type text}, {"Characteristic 3", Text.Trim, type text}, {"Characteristic 4", Text.Trim, type text}, {"Characteristic 5", Text.Trim, type text}, {"Characteristic 6", Text.Trim, type text}, {"Characteristic 7", Text.Trim, type text}}), Custom1 = List.RemoveItems( List.RemoveNulls( List.Combine( Table.ToColumns( #"Trimmed Text"))),{""," "}), rawData = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error), dimTable = Table.Distinct( rawData), #"Merged Queries" = Table.NestedJoin(dimTable, {"Column1"}, rawData, {"Column1"}, "dimTable", JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each Table.RowCount([dimTable])), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"dimTable"}) in #"Removed Columns1"Here is another version with Grouping.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xVXbSsNAEP2V0GfFuezM7j4WBN8U1LfSh1TXGqgJtFX0b/wWv8zpTcUmNItCQx6ykz3ZM2fOTEajAZ4iA7qoEgYng8umTsXVQ3H7mIrhpHlJFvt9j082KO8ichQLDaerfR/v59WinFSzavm2Xl6k+j7NfzwW81QuFtW0fkr1ch2/Lu820Os0q6ZVUxdlfX/WzIuJrdPD+tVNen0uZ0UzrwxVLm3TloJHp0Sej0gBmQJIoNBFoUU5BgI1mLNQF90WGKEXRRbZh3Uyb/sMR7uIFCx2OPeWLwiyiyysmYbBQD5o3OPfoRKbRpavzzuEzJMknEfNgSo7YZTMwzgGBV1RbHFIJwyiZ9Xoj2hbik6s5Ts7p60gwh7JdMqTNggCQyD4n2zbdd5lRU6BY55lKAg79Y7+1ldis8gH9tDT3wRRkFAzrYoEAZ2jVY796H2XgiFansz4oxSHfbs7F8H6iqm7jD3ofA1NtToR91NKIERla2zotx9RHXggypdIMVpzkj9ma5ovBGg9JjPZI9og2/4h+kPHnw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LocationID = _t, #"Characteristic 1" = _t, #"Characteristic 2" = _t, #"Characteristic 3" = _t, #"Characteristic 4" = _t, #"Characteristic 5" = _t, #"Characteristic 6" = _t, #"Characteristic 7" = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"LocationID"}, "Attribute", "Characteristics"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"LocationID", "Attribute"}), #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Characteristics", Text.Trim, type text}}), #"Filtered Rows" = Table.SelectRows(#"Trimmed Text", each ([Characteristics] <> "")), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Characteristics"}, {{"Count", each Table.RowCount(_), Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Characteristics", Order.Ascending}}) in #"Sorted Rows"Hi KAURM ,
Check my sample .pbix file attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
Hi, Anonymous and this is the output I would like to achieve in Power Query, the formula for this in Excel is using a COUNTIF based on the characteristic cell below i.e. A2 and the characteristics range of columns in the sample data.
Thank you! I really appreciate you trying to help!
| Characteristic | Count of characteristics |
| Age | 1926 |
| Disability | 884 |
| Gender | 96 |
| Gender reassignment | 40 |
| None Of The Above | 1073 |
| Race | 91 |
| Religion and/or belief | 239 |
| Sexual orientation | 79 |
Hi KAURM
Based on your sample data, you want the output like this?
If yes, paste the code in Advanced Editor via blank query. I have 2 queries, one for your sample data, one for output, you can see how it was done
rawData as sample data
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xVXbSsNAEP2V0GfFuezM7j4WBN8U1LfSh1TXGqgJtFX0b/wWv8zpTcUmNItCQx6ykz3ZM2fOTEajAZ4iA7qoEgYng8umTsXVQ3H7mIrhpHlJFvt9j082KO8ichQLDaerfR/v59WinFSzavm2Xl6k+j7NfzwW81QuFtW0fkr1ch2/Lu820Os0q6ZVUxdlfX/WzIuJrdPD+tVNen0uZ0UzrwxVLm3TloJHp0Sej0gBmQJIoNBFoUU5BgI1mLNQF90WGKEXRRbZh3Uyb/sMR7uIFCx2OPeWLwiyiyysmYbBQD5o3OPfoRKbRpavzzuEzJMknEfNgSo7YZTMwzgGBV1RbHFIJwyiZ9Xoj2hbik6s5Ts7p60gwh7JdMqTNggCQyD4n2zbdd5lRU6BY55lKAg79Y7+1ldis8gH9tDT3wRRkFAzrYoEAZ2jVY796H2XgiFansz4oxSHfbs7F8H6iqm7jD3ofA1NtToR91NKIERla2zotx9RHXggypdIMVpzkj9ma5ovBGg9JjPZI9og2/4h+kPHnw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LocationID = _t, #"Characteristic 1" = _t, #"Characteristic 2" = _t, #"Characteristic 3" = _t, #"Characteristic 4" = _t, #"Characteristic 5" = _t, #"Characteristic 6" = _t, #"Characteristic 7" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"LocationID", type text}, {"Characteristic 1", type text}, {"Characteristic 2", type text}, {"Characteristic 3", type text}, {"Characteristic 4", type text}, {"Characteristic 5", type text}, {"Characteristic 6", type text}, {"Characteristic 7", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"LocationID"}),
#"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Characteristic 1", Text.Trim, type text}, {"Characteristic 2", Text.Trim, type text}, {"Characteristic 3", Text.Trim, type text}, {"Characteristic 4", Text.Trim, type text}, {"Characteristic 5", Text.Trim, type text}, {"Characteristic 6", Text.Trim, type text}, {"Characteristic 7", Text.Trim, type text}}),
Custom1 = List.RemoveItems( List.RemoveNulls( List.Combine( Table.ToColumns( #"Trimmed Text"))),{""," "}),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"this is the output
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYyxCgIxEER/ZUkt+A0Hgp0HahdSbLy5uBB3IYmif2+IV9jNmxme925KcGHn3UEqR8nSPgOP0AXlL1IB1ypJH9A2+pMpaF7pegdN0V4/z5lvW0CWJKbEuuytUOyMdUwXvJ+cyYp0F7d+ciF8AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Characteristic " = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Characteristic ", type text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Characteristic "}, rawData, {"Column1"}, "rawData", JoinKind.LeftOuter),
#"Added Custom" = Table.AddColumn(#"Merged Queries", "Count", each Table.RowCount([rawData])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"rawData"})
in
#"Removed Columns"
Hi, Anonymous what do I need to replace the below with? is it the query name that I am referring the data from or the query name and the step? as I have tried to use "#characteristics in the past 12 months" but the code is coming up with a Expression.SyntaxError: Token Eof expected.
TYyxCgIxEER/ZUkt+A0Hgp0HahdSbLy5uBB3IYmif2+IV9jNmxme925KcGHn3UEqR8nSPgOP0AXlL1IB1ypJH9A2+pMpaF7pegdN0V4/z5lvW0CWJKbEuuytUOyMdUwXvJ+cyYp0F7d+ciF8AQ=="
My Power Query Statements look like the following
Thank you once again!
Hi KAURM
The source was done by Enter Data, so it was generated by Power Query. I have two queries, one is called rawData which is your sample data, the other is dimTable which is the output. To make things easier, you right click to open a blank query, and go to Advanced Editor, paste the whole code. Then you can see the steps which you can apply to your original data
To make it easier, I put them in one query, if you still can't get it, pm your email, I will send you the sample .pbix file
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xVXbSsNAEP2V0GfFuezM7j4WBN8U1LfSh1TXGqgJtFX0b/wWv8zpTcUmNItCQx6ykz3ZM2fOTEajAZ4iA7qoEgYng8umTsXVQ3H7mIrhpHlJFvt9j082KO8ichQLDaerfR/v59WinFSzavm2Xl6k+j7NfzwW81QuFtW0fkr1ch2/Lu820Os0q6ZVUxdlfX/WzIuJrdPD+tVNen0uZ0UzrwxVLm3TloJHp0Sej0gBmQJIoNBFoUU5BgI1mLNQF90WGKEXRRbZh3Uyb/sMR7uIFCx2OPeWLwiyiyysmYbBQD5o3OPfoRKbRpavzzuEzJMknEfNgSo7YZTMwzgGBV1RbHFIJwyiZ9Xoj2hbik6s5Ts7p60gwh7JdMqTNggCQyD4n2zbdd5lRU6BY55lKAg79Y7+1ldis8gH9tDT3wRRkFAzrYoEAZ2jVY796H2XgiFansz4oxSHfbs7F8H6iqm7jD3ofA1NtToR91NKIERla2zotx9RHXggypdIMVpzkj9ma5ovBGg9JjPZI9og2/4h+kPHnw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LocationID = _t, #"Characteristic 1" = _t, #"Characteristic 2" = _t, #"Characteristic 3" = _t, #"Characteristic 4" = _t, #"Characteristic 5" = _t, #"Characteristic 6" = _t, #"Characteristic 7" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"LocationID", type text}, {"Characteristic 1", type text}, {"Characteristic 2", type text}, {"Characteristic 3", type text}, {"Characteristic 4", type text}, {"Characteristic 5", type text}, {"Characteristic 6", type text}, {"Characteristic 7", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"LocationID"}), #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Characteristic 1", Text.Trim, type text}, {"Characteristic 2", Text.Trim, type text}, {"Characteristic 3", Text.Trim, type text}, {"Characteristic 4", Text.Trim, type text}, {"Characteristic 5", Text.Trim, type text}, {"Characteristic 6", Text.Trim, type text}, {"Characteristic 7", Text.Trim, type text}}), Custom1 = List.RemoveItems( List.RemoveNulls( List.Combine( Table.ToColumns( #"Trimmed Text"))),{""," "}), rawData = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error), dimTable = Table.Distinct( rawData), #"Merged Queries" = Table.NestedJoin(dimTable, {"Column1"}, rawData, {"Column1"}, "dimTable", JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each Table.RowCount([dimTable])), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"dimTable"}) in #"Removed Columns1"Hi Anonymous
That's great thank you!
I have changed the data source to #"characteristics in the last 12 months analysis" but now the following error occurs