Grouping column text values in Power Query

[KAURM]

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 using Modelling) 

So ideally I would like the end product to look like this

Characteristic  | Count (from all Characteristic 1-7 column) as the second column

Age                   

Gender

etc. 

If anyone can help I would really appreciate it! I have been trying to do this for two days now!

I am more of an excel user than a Power Query so I am on a steep learning curve...

Thank you in advance!

@v-kelly-msft @v-kellf @mahoneypat @v-stephen-msft @v-yingjl @watkinnc @Payeras_BI @AlexisOlson @Jakinta @Vera_33 @Jakinta @edhans @Fowmy @CNENFRNL 

[Vera_33 · Accepted solution]

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"

[Jakinta · Accepted solution]

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"

[KAURM]

Thank you @Vera_33 @Jakinta @v-kelly-msft 

I finally did it! I couldn't have done it without your help and going through step by step I understand the steps and how to change the data in a way that Power Query understands it is a variable. So thank you so much! 

[v-kelly-msft · Accepted solution]

Hi @KAURM ,

Check my sample .pbix file attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Syndicate_Admin]

Hi @v-kelly-msft Thank you for your message

I get this issue

[KAURM]

It worked when I took out the steps to add custom column from the unpivoted columns as all my data once unpivoted was already in two columns! Thank you so much! 

[KAURM]

Hi @v-kelly-msft Thank you for your message

I get this issue

[Jakinta · Accepted solution]

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"

[KAURM]

Hi @Jakinta I also get this error too:

[Syndicate_Admin]

Just convert the column to number type in step before, as it is suggested in error description. I really dont even remember if sorting was there optional and if it was even necessary. Note: Please try to read and understand PQ error descriptions, they always give you a hint to solution.

[Jakinta]

Just convert the column to number type in step before, as it is suggested in error description. I really dont even remember if sorting was there optional and if it was even necessary. Note: Please try to read and understand PQ error descriptions, they always give you a hint to solution.

[Jakinta]

And practice, practice, practice...

"Ever tried? Ever failed? No matter. Try again. Fail again. Fail better."

Samuel Beckett

[KAURM]

@Jakinta thank you for your reply

I get the following error when I try to use this code:

[Vera_33]

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. 

[Syndicate_Admin]

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

[KAURM]

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

[KAURM]

Hi, @Vera_33 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

[Vera_33]

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"

[KAURM]

Hi, @Vera_33  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! 

[Vera_33 · Accepted solution]

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"

[KAURM]

Hi @Vera_33 

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

[Vera_33]

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.