Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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. Group 1 is all the numeric ones.  G2 alphanume...
Need Help
Show and Tell
Tips and Tricks
  • edhans's avatar
    edhans
    6 years ago

    Hi Anonymous - 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.

     

    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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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:

 

 

 

gives the following result