Groupby with a Conditional Statement to create Col Table

[roncruiser]

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 statement to run between the two functions.
I'm missing some fundamental step I am not aware of.  I need some help from the forum.

Right now, the groupby step works perfectly, but only using a single function.  Highlighted in yellow:

How do I add an if statment to the groupby step (within the step itself) to run the specific function depending on the value of column "D" to add the table to the "x" column?
Example: If [D] = 0 then Fn_MarkD0 else Fn_MarkD1.

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]})

Example pbix:
https://drive.google.com/file/d/1jOQ0gBE6Y-bJu1eNeftQE-SOcszDQjnK/view?usp=sharing

Thank You!

[dufoq3 · Accepted solution]

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]})

[dufoq3 · Accepted solution]

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]})

[roncruiser]

I tried this too.
As a result, the groupby tables look fine but the if statement does not recognize x[D] so it's always see a false and defaulting to the else fn. Bottom line, it's not working...?

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

[lbendlin]

can you dumb down the problem for me?  Remove anything not related to the issue, simplify the functions etc.

[roncruiser]

@lbendlin 

I've reduced the functions to a simple form.

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) => 
        [lstBuffer = List.Buffer(lst),    
        replace_first = List.ReplaceValue(lstBuffer, "0","00", Replacer.ReplaceText)]
        [replace_first],

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

        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,8 ) & 
                    List.Transform(List.RemoveFirstN(to_col, 8), Fn_MarkD0 ),  
//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]})
in
    GroupRunFunctionFn

Example:
Fn_MarkD0 will replace all "0" with "00".

Example:
Fn_MarkD1 will replace all "0" with "11".  Edit: Corrected.  Thanks.

Thank You @lbendlin 

[lbendlin · Accepted solution]

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"

[roncruiser]

@lbendlin  It worked well.

As always, thank you so much for the help!
This forum is awesome as always.

[roncruiser]

@lbendlin 
Thank you!  I'll check it out soon.

[roncruiser]

@lbendlin sure, will do....

[roncruiser]

Adding a simple If statement doesn't help:

Resulting table "error" in column x:

That would be the most logical place for the condtional statement.  To me.

        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), if [D] = 0 then Fn_MarkD0 else Fn_MarkD1), 

                    Table.ColumnNames(x))
                    ] [result1]}
                    )

Still perplexed!

[lbendlin]

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

[roncruiser]

@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]

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.