Forum Discussion

KAURM's avatar
KAURM
Helper I
5 years ago
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 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 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
    Jakinta
    5 years ago

    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"

     

20 Replies

  • KAURM's avatar
    KAURM
    Helper I
    5 years ago

    Thank you Anonymous 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! 

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Syndicate_Admin
      Administrator
      5 years ago

      Hi @Vera_33

       

      this is the sample data

       

      LocationIDCharacteristic 1Characteristic 2Characteristic 3Characteristic 4Characteristic 5Characteristic 6Characteristic 7
      1-130149658None Of The Above      
      1-137491395Age Disability Gender Gender reassignment Race Religion and/or belief Sexual orientation
      1-714622735Age Disability Gender Gender reassignment Race Religion and/or belief Sexual orientation
      1-132805828Age Disability     
      1-302062804Disability Gender     
      1-217561355Disability Religion and/or belief    
      1-2399992260Race Religion and/or belief    
      1-5134935368None Of The Above      
      1-118278695Disability      
      1-336286137None Of The Above      
      1-129132538None Of The Above      
      1-4066345315None Of The Above      
      1-123986067Sexual orientation      
      1-109736697Age Disability Gender Gender reassignment Race Religion and/or belief Sexual orientation
      1-129459655Age Disability     
      1-353712634None Of The Above      
      1-4851030820Age Disability Gender Gender reassignment Race Sexual orientation
      1-122460397None Of The Above      
      1-285346742Disability Religion and/or belief    
      1-5462783705Disability      
      1-2095121638None Of The Above      
      1-120814427Religion and/or belief      
      1-4309674331Age Sexual orientation    
      1-121025332Age Disability Religion and/or belief   
      1-132660323Disability      
      1-5089632910Disability      
      1-116407022Religion and/or belief      
      1-619097277Age Disability Gender Gender reassignment Race Religion and/or belief Sexual orientation
      1-109550295Religion and/or belief      
      1-114061355Religion and/or belief      
    • KAURM's avatar
      KAURM
      Helper I
      5 years ago

      Hi Anonymous

       

      this is the sample data

       

      LocationIDCharacteristic 1Characteristic 2Characteristic 3Characteristic 4Characteristic 5Characteristic 6Characteristic 7
      1-130149658None Of The Above      
      1-137491395Age Disability Gender Gender reassignment Race Religion and/or belief Sexual orientation
      1-714622735Age Disability Gender Gender reassignment Race Religion and/or belief Sexual orientation
      1-132805828Age Disability     
      1-302062804Disability Gender     
      1-217561355Disability Religion and/or belief    
      1-2399992260Race Religion and/or belief    
      1-5134935368None Of The Above      
      1-118278695Disability      
      1-336286137None Of The Above      
      1-129132538None Of The Above      
      1-4066345315None Of The Above      
      1-123986067Sexual orientation      
      1-109736697Age Disability Gender Gender reassignment Race Religion and/or belief Sexual orientation
      1-129459655Age Disability     
      1-353712634None Of The Above      
      1-4851030820Age Disability Gender Gender reassignment Race Sexual orientation
      1-122460397None Of The Above      
      1-285346742Disability Religion and/or belief    
      1-5462783705Disability      
      1-2095121638None Of The Above      
      1-120814427Religion and/or belief      
      1-4309674331Age Sexual orientation    
      1-121025332Age Disability Religion and/or belief   
      1-132660323Disability      
      1-5089632910Disability      
      1-116407022Religion and/or belief      
      1-619097277Age Disability Gender Gender reassignment Race Religion and/or belief Sexual orientation
      1-109550295Religion and/or belief      
      1-114061355Religion and/or belief      
      • KAURM's avatar
        KAURM
        Helper I
        5 years ago

        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
        Age1926
        Disability884
        Gender96
        Gender reassignment40
        None Of The Above1073
        Race91
        Religion and/or belief239
        Sexual orientation79
  • Jakinta's avatar
    Jakinta
    Solution Sage
    5 years ago

    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's avatar
      KAURM
      Helper I
      5 years ago

      Jakinta thank you for your reply

       

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

       

       

      • Jakinta's avatar
        Jakinta
        Solution Sage
        5 years ago

        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.

    • KAURM's avatar
      KAURM
      Helper I
      5 years ago

      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!