Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Sort by number in a grouped text field

    Posted Oct 24, 2022 08:50 PM

    Dear Power Bi Community,

    I have an issue with the group by function where the combined values are combination of 2 fields :

    Field 1 = numbers

    Field 2 = text

     

    I've used the following formula to group rows :

    = Table.Group(#"customized column", {"FactorA", "FactorB"}, {{"Combined value", each Text.Combine((List.Sort( [Field1 & Field2], Order.Ascending),"#(lf)"), type nullable text}})

     

    with this formula, rows into each combined value is sorted but as following :

    1 xxxx

    10 xxxx

    2 xxxx

    while I'm looking for an order as :

    1 xxxx

    2 xxxx

    10 xxxx

    Any ideas to help with are more than welcome !

    Many thanks in advance for your help !  

    Regards,



    ------------------------------
    EMMY ALAMI
    Data analyst
    ------------------------------


  • 2.  RE: Sort by number in a grouped text field

    Posted Oct 25, 2022 12:21 AM
    Check the data format of that number column 





  • 3.  RE: Sort by number in a grouped text field

    Posted Oct 25, 2022 05:03 AM
    HI,
    It's a number bit once mergerd with the etxt format it's a text
    Thanks for your reply !

    ------------------------------
    EMMY ALAMI
    Data analyst
    ------------------------------



  • 4.  RE: Sort by number in a grouped text field

    Posted Oct 25, 2022 05:51 AM
    Can you sort before the combine?

    ------------------------------
    Fredy Chen
    ------------------------------



  • 5.  RE: Sort by number in a grouped text field

    Bronze Contributor
    Posted Oct 26, 2022 09:38 AM
    If you like this approach (which I do, if it fits your problem), take care that sorting is not guaranteed to work if you don't apply a buffer step. Imke Feldman raised that here:

    https://community.powerbi.com/t5/Community-Blog/Bug-warning-for-Table-Sort-and-removing-duplicates-in-Power/ba-p/810390

    She also has a link to her dialogue with MS on it.

    ------------------------------
    Amon Seagull
    McKinney
    ------------------------------



  • 6.  RE: Sort by number in a grouped text field

    Posted Oct 25, 2022 06:04 AM
    The try to convert fixed text 001, 002...






  • 7.  RE: Sort by number in a grouped text field

    Silver Contributor
    Posted Oct 25, 2022 05:17 PM
    If you still have the number column in your table, you can use it to sort. Go to the data view and select the text column you want to sort. Select the Column Tools menu and click on the button for "Sort by Column". Select your number column and now this new text field will be sorted by the number column.

    ------------------------------
    Nancy Peterson
    Senior Business Analyst
    Pitsco Education
    Pittsburg KS
    ------------------------------



  • 8.  RE: Sort by number in a grouped text field

    Posted Oct 26, 2022 03:54 PM
    Many thanks !
    I finally used the method of adding leading zeros for Nbres that are <9 so at the end I have a list : 01 / 02/ 03/ .... / 10 / 11/ ...
    and it works :)
    Thanks again !

    ------------------------------
    EMMY ALAMI
    Data analyst
    ------------------------------