Forum Discussion
Grouping column text values in Power Query
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 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
Hi KAURM
It is not just to change the Source, you need to go through the steps and understand how they work. The error might not happen at the last step, you need to click each step to understand it. My guess is it happened in the second step as your sample data was entered but your real source may be reading from somewhere, you need to transform it to the same shape then go next step. I will send you my sample .pbix file.