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,
Kelly

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