Forum Discussion

roncruiser's avatar
roncruiser
Post Patron
2 years ago
Solved

Groupby with a Conditional Statement to create Col Table

I have a groupby step where I'd like to utilize two specific functions depending on the value of column "D".  "D" will either a one or zero. I've tried to my knowledge limit adding the conditional...
Need Help
Show and Tell
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi roncruiser, replace GroupRunFunctionFn step with this and let me know.

    Comment: your functions Fn_MarkD0 and Fn_MarkD1 are exactly the same.

     

     

    = Table.Group(ChangedType, { "V", "C", "R", "P", "D", "B"}, {"x",  (x)  => 
           [ to_col = Table.ToColumns(Table.Sort(x, "idx")),
             result1 =Table.FromColumns(
                List.FirstN(to_col,  & 
                    List.Transform(List.RemoveFirstN(to_col, 8), (y)=> if x[D]{0} = 0 then Fn_MarkD0(y) else Fn_MarkD1(y) ),  //If statement to run between functions Depending on value of column "D":  If [D] = 0 then Fn_MarkD0 else Fn_MarkD1
                    Table.ColumnNames(x))
           ][result1]})

     

  • lbendlin's avatar
    lbendlin
    2 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZhZbgQhDETv0t/5wIBZzjLK/a8RI6UJWxFbmpYYaagG82zX8Pk87vnaHhrG42f4/v312X46fue4TnTj8J3uwXSK6bCu+Xk1AtDwldUaEW5j3SDWWN/2zix13SXWWHfd4xEoq0XWX3aR4vS7KSiqId4FRpEKj8awEkJ4BraoIFIjsf58CAEbC+uJJYQsc1njflFB0KZAa+AvKgjb7NgQXQRuzsWggsgtvG32ooLQrT4aoovYrdWwI4/YJZc3li4yCF6iGPWR8bDcesoGGYQv+VL15HnEL4W08XSRQQBTDNuGLzKw9LLbTvEiA4svJ0Nue8QwJamdehkEMaXi9A0lQIqzjysMFxlIcU5bSbzIQIpzvR74rAIhLt7dFjKrQIZLnNb5LAbqT2KdTcP40ZuvcSrz9tbDbI9ebKiSAWj4qqcrAg02oMVAo1R9WUtAgyztNCOR4vS7KUBEvJe+oFUkUg0rmbzXqCLey6CCQBXvZejrCFjxXga/g5BlNvh0QtCmsHXHiwrCVryX/r8LIXDFexlUELnivQyuCaFbvcVlI3bFe+l3NHXtKRfFexn8BYK3eS9DR4flVryXQQbhS6a89ohf8V4Wt4MAbt7LEGJYesV7GXwpLL6cqsGzI4ab9zIYSgRx8176ejV5r0lGvJc+GQKkWLyXQQZSLN5Lf1IBUizmSx/iACkW92VYDaS4pGtOzSoQ4pImN7R+4N3C+0h/4+dgA3+/wFuF92Eu6RlWvA0vdwrvkywXUBCo7OKJ/LMIqh85Z70IWrR0tVO4zyKoPAvyp0WeRVDK16rfDizNraVBQjcVVAtbR1NHBf7Vag3tojKLIGbbVcJ6dpMGvkboEiHRdAt9nr/ZyncsaVe2Wf+k3ThfDOW+6mE30Eu+Y/GSp5M6nsZmJXu2OD7F+CyylrAukoteZO3C71hSTp8taw/u2eJZzflmI7tI1W9nc5F9LCl3Qu+ssrbfPpaUU0dl85B/2SJtXK2CgCVwLX9WQdS2tDsVzOnn0ED2sRjIS3TnpSBsm330D8jfloHfPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [V = _t, F = _t, C = _t, R = _t, P = _t, D = _t, B = _t, idx = _t, h = _t, g = _t, f = _t, e = _t, d = _t, c = _t, b = _t, a = _t]),
    
ChangedType = Table.TransformColumnTypes(Source,{{"V", Int64.Type}, {"F", Int64.Type}, {"C", Int64.Type}, {"R", Int64.Type}, {"P", Int64.Type}, {"D", Int64.Type}, {"B", Int64.Type}, {"idx", Int64.Type}}),

//Functions********************************
//Function Fn_MarkD0  If [D] = 0 run this function:
        Fn_MarkD0 = (lst) => 
        List.ReplaceValue(lst, "0","00", Replacer.ReplaceText),

//Function1 Fn_MarkD1  If [D] = 1 run this function:
        Fn_MarkD1 = (lst) => 
        List.ReplaceValue(lst, "0","11", Replacer.ReplaceText),
//End Functions********************************

        GroupRunFunctionFn =  Table.Group(
        ChangedType, { "V", "C", "R", "P", "D", "B"}, 
                    {"x",  (x)  => 
           let to_col = Table.ToColumns(Table.Sort(x, "idx")) 
           in Table.FromColumns(
                List.FirstN(to_col,8 ) & 
                    List.Transform(List.RemoveFirstN(to_col, 8), if x[D]{0}=0 then Fn_MarkD0 else Fn_MarkD1),  
//If statement to run between functions Depending on value of column "D":  If [D] = 0 then //Fn_MarkD0 else Fn_MarkD1
                    Table.ColumnNames(x))})
in
    GroupRunFunctionFn

     

    Fn_MarkD1 will replace all "1" with "11".

    no, it will replace "0" with "11"

lbendlin's avatar
lbendlin
Super User
2 years ago

Might be easier to first group by D with all rows, and then apply your functions on each of the results.

  • roncruiser's avatar
    roncruiser
    Post Patron
    2 years ago

    lbendlin 
    I see your point.  I do not prefer to.  The integrity of the data must remain intact after unpacking the x column.  The values of column D are integral to one another.  I prefer not to add step further down stream if not needed.

    There has to be a way to do this within the groupby step itself.  I'm just not seasoned enough to figure it out myself.  Yet.

    AlienSx could use some help.

    • lbendlin's avatar
      lbendlin
      Super User
      2 years ago

      Feels like you are trying way too hard. For example 

       

      GroupRunFunctionFn = Table.Group(
ChangedType, { "V", "C", "R", "P", "D", "B"},
{"x", (x) =>
[ to_col = Table.ToColumns(Table.Sort(x, "idx")),
result1 =Table.FromColumns(
List.FirstN(to_col,  &
List.Transform(List.RemoveFirstN(to_col, 8), Fn_MarkD0 ), 
Table.ColumnNames(x))
] [result1]})

       

      is better written as

      GroupRunFunctionFn = Table.Group(
ChangedType, { "V", "C", "R", "P", "D", "B"},
{"x", (x) =>
let  to_col = Table.ToColumns(Table.Sort(x, "idx"))
in Table.FromColumns(
List.FirstN(to_col,  &
List.Transform(List.RemoveFirstN(to_col, 8), Fn_MarkD0 ), 
Table.ColumnNames(x))
})

       

      But the whole thing is too complex for me to understand if you could write 

       

      if x[D]=0 then FN_MarkD0 else FN_MarkD1

       

      or if you would need to go to Expression.Evaluate.