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 KAURM
Can you put some sample data in a format which we can copy? And put the expected reuslt as well? You can put them in Excel and paste here.
Hi @Vera_33
this is the sample data
LocationID Characteristic 1 Characteristic 2 Characteristic 3 Characteristic 4 Characteristic 5 Characteristic 6 Characteristic 7 1-130149658 None Of The Above 1-137491395 Age Disability Gender Gender reassignment Race Religion and/or belief Sexual orientation 1-714622735 Age Disability Gender Gender reassignment Race Religion and/or belief Sexual orientation 1-132805828 Age Disability 1-302062804 Disability Gender 1-217561355 Disability Religion and/or belief 1-2399992260 Race Religion and/or belief 1-5134935368 None Of The Above 1-118278695 Disability 1-336286137 None Of The Above 1-129132538 None Of The Above 1-4066345315 None Of The Above 1-123986067 Sexual orientation 1-109736697 Age Disability Gender Gender reassignment Race Religion and/or belief Sexual orientation 1-129459655 Age Disability 1-353712634 None Of The Above 1-4851030820 Age Disability Gender Gender reassignment Race Sexual orientation 1-122460397 None Of The Above 1-285346742 Disability Religion and/or belief 1-5462783705 Disability 1-2095121638 None Of The Above 1-120814427 Religion and/or belief 1-4309674331 Age Sexual orientation 1-121025332 Age Disability Religion and/or belief 1-132660323 Disability 1-5089632910 Disability 1-116407022 Religion and/or belief 1-619097277 Age Disability Gender Gender reassignment Race Religion and/or belief Sexual orientation 1-109550295 Religion and/or belief 1-114061355 Religion and/or belief Hi Anonymous
this is the sample data
LocationID Characteristic 1 Characteristic 2 Characteristic 3 Characteristic 4 Characteristic 5 Characteristic 6 Characteristic 7 1-130149658 None Of The Above 1-137491395 Age Disability Gender Gender reassignment Race Religion and/or belief Sexual orientation 1-714622735 Age Disability Gender Gender reassignment Race Religion and/or belief Sexual orientation 1-132805828 Age Disability 1-302062804 Disability Gender 1-217561355 Disability Religion and/or belief 1-2399992260 Race Religion and/or belief 1-5134935368 None Of The Above 1-118278695 Disability 1-336286137 None Of The Above 1-129132538 None Of The Above 1-4066345315 None Of The Above 1-123986067 Sexual orientation 1-109736697 Age Disability Gender Gender reassignment Race Religion and/or belief Sexual orientation 1-129459655 Age Disability 1-353712634 None Of The Above 1-4851030820 Age Disability Gender Gender reassignment Race Sexual orientation 1-122460397 None Of The Above 1-285346742 Disability Religion and/or belief 1-5462783705 Disability 1-2095121638 None Of The Above 1-120814427 Religion and/or belief 1-4309674331 Age Sexual orientation 1-121025332 Age Disability Religion and/or belief 1-132660323 Disability 1-5089632910 Disability 1-116407022 Religion and/or belief 1-619097277 Age Disability Gender Gender reassignment Race Religion and/or belief Sexual orientation 1-109550295 Religion and/or belief 1-114061355 Religion and/or belief 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"
