topic Re: Grouping column text values in Power Query in Power Query

Grouping column text values in Power Query

KAURM — Fri, 09 Jul 2021 11:00:25 GMT

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 Anonymous v-yingjl Anonymous Payeras_BI AlexisOlson Jakinta Anonymous Jakinta edhans Fowmy CNENFRNL

Re: Grouping column text values in Power Query

Anonymous — Fri, 09 Jul 2021 11:47:28 GMT

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.

Re: Grouping column text values in Power Query

KAURM — Fri, 09 Jul 2021 13:22:10 GMT

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

Re: Grouping column text values in Power Query

KAURM — Fri, 09 Jul 2021 13:29:25 GMT

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

Re: Grouping column text values in Power Query

Anonymous — Fri, 09 Jul 2021 13:49:05 GMT

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"

Re: Grouping column text values in Power Query

KAURM — Fri, 09 Jul 2021 14:30:45 GMT

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!

Re: Grouping column text values in Power Query

Anonymous — Fri, 09 Jul 2021 14:56:55 GMT

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"

Re: Grouping column text values in Power Query

Syndicate_Admin — Fri, 09 Jul 2021 15:01:52 GMT

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

Re: Grouping column text values in Power Query

Jakinta — Fri, 09 Jul 2021 15:34:08 GMT

Here is another version with Grouping.

Re: Grouping column text values in Power Query

KAURM — Mon, 12 Jul 2021 10:32:10 GMT

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

Re: Grouping column text values in Power Query

KAURM — Mon, 12 Jul 2021 12:33:59 GMT

Jakinta thank you for your reply

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

Re: Grouping column text values in Power Query

Anonymous — Tue, 13 Jul 2021 01:06:33 GMT

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.

Re: Grouping column text values in Power Query

v-kelly-msft — Tue, 13 Jul 2021 04:21:34 GMT

Hi KAURM ,

Check my sample .pbix file attached.

Best Regards,
Kelly

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

Re: Grouping column text values in Power Query

KAURM — Tue, 13 Jul 2021 08:20:13 GMT

Hi v-kelly-msft Thank you for your message

I get this issue

Re: Grouping column text values in Power Query

KAURM — Tue, 13 Jul 2021 08:26:51 GMT

Hi Jakinta I also get this error too:

Re: Grouping column text values in Power Query

KAURM — Tue, 13 Jul 2021 08:47:25 GMT

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!

Re: Grouping column text values in Power Query

Syndicate_Admin — Tue, 13 Jul 2021 09:56:54 GMT

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

I get this issue

Re: Grouping column text values in Power Query

Jakinta — Tue, 13 Jul 2021 11:02:24 GMT

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.

Re: Grouping column text values in Power Query

Jakinta — Tue, 13 Jul 2021 11:05:29 GMT

And practice, practice, practice...

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

Samuel Beckett

Re: Grouping column text values in Power Query

Syndicate_Admin — Tue, 13 Jul 2021 12:46:06 GMT