cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

group filter to sort similar value

Hi,

I'm looking forward to sorting my employee column in a certain order.

Here is the list of employees that I want to be sorted into a group.

Emp#.PNG

Group 1 is all the numeric ones. 

G2 alphanumeric with one character(C to Z) and numbers. 

Lastly, two-character and rest numeric. 

 

Please let me know if it's possible or no? 

 

Thank you for helping. 

1 ACCEPTED SOLUTION
Super User III
Super User III

Hi @mannymann - this is certianly possible. Here is one way. This simply goes through the data converting the employee "number" (I put that in quotes as your employee numbers are text, which is usually the best way to handle it) to a numberic value and if it works, it classifies it as "Numberic." If it fails, it removes the first character and tries again. If it works, it is 1 char, otherwise, it tries again removing the first two characters. It doesn't classifiy anything starting with A or B. Leaves those null. You can change that in the M code below.

edhans_0-1594659872959.png

 

The full M code of my example is here.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVorViVYyMTUD0+YWlmA6GShhAmEZw+SSk2Gqk5NhYknlYFYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee Number", type text}}),
    #"Added Grouping" = 
        Table.AddColumn(
            #"Changed Type", 
            "Grouping", each 
            if List.Contains({"a".."b"}, Text.Start([Employee Number], 1)) then null
            else if (try Number.FromText([Employee Number]))[HasError] = false then "Numeric"
            else if (try Number.FromText(
                            Text.End([Employee Number], Text.Length([Employee Number]) -1)
                            )
                    )[HasError] = false then "One Letter"
            else if (try Number.FromText(
                            Text.End([Employee Number], Text.Length([Employee Number]) -2)
                            )
                    )[HasError] = false then "Two Letters" else null
        )
in
    #"Added Grouping"

 

 1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

7 REPLIES 7
Super User II
Super User II

my previous post refer to somethink that seem to give answer to that request.

 

look the picture sequence  and tell me where you would somethink different  (naming aside).

 

image.png

 

image.pngimage.png

 

 

image.pngimage.png

 

 

 

 

Super User II
Super User II

I use the table.partition function with the help of a function built for the occasion that "classifies" text strings.

 

I believe that the technique can be of general validity and also apply to other situations in which rows of tables must be grouped on the basis of "complex" criteria

 

here the function typedStr

 

let
tipizza=(str,optional firstN,optional lastN)=>
let
      lst= if firstN<> null and lastN<>null then Text.ToList(Text.End(Text.Start(str,firstN),lastN)) else 
           if firstN<> null and lastN=null  then Text.ToList(Text.Start(str,firstN)) else 
           if firstN= null and lastN<>null  then Text.ToList(Text.End(str,lastN)) else 
           Text.ToList(str), 
      tpz=List.Transform(lst, each if List.Contains({"a".."z"}, _) then "char" else if List.Contains({"0".."9"}, _) then "digit" else null)
in
 tpz
 in tipizza

 

 

here the rest of code

 

 

let

hash={{"digit","digit"},{"char","digit"},{"char","char"}},
part=Table.Partition(
    Table.FromRecords({
        [a = "9020", b = 4],
        [a = "9021", b = 4],
        [a = "9494", b = 4],
        [a = "c1494", b = 4],
        [a = "c38696", b = 4],
        [a = "c3855", b = 4],
        [a = "z3467", b = 4],
        [a = "ca38", b = 4],
        [a = "zz386", b = 4],
        [a = "vc386", b = 4],
        [a = "ff179844", b = 4]
    }),
    "a",
    3,
    each List.PositionOf(hash,typedStr(_,2))
),
tab=Table.FromList(part,Splitter.SplitByNothing(),null,null),
    #"Added Index" = Table.AddIndexColumn(tab, "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each hash{[Index]}),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ":"), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Index"}),
    #"Expanded Column1" = Table.ExpandTableColumn(#"Removed Columns", "Column1", {"a"}, {"Column1.a"})
in
    #"Expanded Column1"

 

 

which starting from:

 

image.png

 

 

gives the following result

 

image.png

Super User I
Super User I

@mannymannanother solution without using lists, by using Text.Length, Text.Select and the sketchy text comparisons.

 

    #"Added Employee Group" =
        Table.AddColumn(
            Source,
            "Employee Group",
            each
                let
                    CorrectTextFormat = Text.Upper(Text.From([Employee Number])),
                    Result =
                        Text.Length(Text.Select(CorrectTextFormat, {"A".."Z"}))
                        + 1,
                    CheckIfInGroup = (CorrectTextFormat >= "C" or CorrectTextFormat <= "9") and Result < 4
                in
                    if CheckIfInGroup then
                        "G" & Text.From(Result)
                    else
                        null
        )

 

 

Best,




Feel free to connect with me:
LinkedIn

Super User III
Super User III

Hi @mannymann - this is certianly possible. Here is one way. This simply goes through the data converting the employee "number" (I put that in quotes as your employee numbers are text, which is usually the best way to handle it) to a numberic value and if it works, it classifies it as "Numberic." If it fails, it removes the first character and tries again. If it works, it is 1 char, otherwise, it tries again removing the first two characters. It doesn't classifiy anything starting with A or B. Leaves those null. You can change that in the M code below.

edhans_0-1594659872959.png

 

The full M code of my example is here.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVorViVYyMTUD0+YWlmA6GShhAmEZw+SSk2Gqk5NhYknlYFYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee Number", type text}}),
    #"Added Grouping" = 
        Table.AddColumn(
            #"Changed Type", 
            "Grouping", each 
            if List.Contains({"a".."b"}, Text.Start([Employee Number], 1)) then null
            else if (try Number.FromText([Employee Number]))[HasError] = false then "Numeric"
            else if (try Number.FromText(
                            Text.End([Employee Number], Text.Length([Employee Number]) -1)
                            )
                    )[HasError] = false then "One Letter"
            else if (try Number.FromText(
                            Text.End([Employee Number], Text.Length([Employee Number]) -2)
                            )
                    )[HasError] = false then "Two Letters" else null
        )
in
    #"Added Grouping"

 

 1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Thank you for the post and appreciate your helping but, it doesn't meet the requirements. 

We have a column that has multiple values such as numeric and alphanumeric. We want to create a new column where we can select the filter options that will result in just numbers, starting A character or first two characters sorting functionality. 

 

Please let me know if that is possible. 

 

Thank you

I don't understand if your original problem is solved or not.

Could you give some input table  and expected results, possibly in a format wich is easily copyable?

 

Emp#.PNG

here is my employee column and on the left-hand side G1, G2 and G3.  I would like G1, G2..... Gx is a new column so that I can filter them out as an option and select what I want to pick. 

For example, G3 includes 2 characters starting with C. I want that as one group. 

Numeric under the G1. 

Does this make sense? 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors