Forum Discussion

bbrimberry's avatar
bbrimberry
New Member
4 years ago
Solved

help with custom function counter

I'm new to power query and would love some help with this custom fuction counter

 

 

 

 

i have the below M code.

 

 

 

(totalLoops as number, optional Loop as number, optional Value as number, optional counter as number) =>
let

    currentValue =
    if Value = 0 then counter = counter +1

    else counter=counter,
    currentLoop =
    if Loop is null then 1
    else Loop + 1,
    output =
        if currentLoop = totalLoops
        then counter
        else @#"fxLoop - 1"(totalLoops, currentLoop, currentValue)
in
    output

 

below is the desired result. 

when a 0 is found start counting at 1 until the next 0 

at the next 0 output a 2 and so on....

 

 

 

 

Need Help
  • CNENFRNL's avatar
    CNENFRNL
    4 years ago

    What's the point constructing a recursive func whereas there's simple native Table.Group() available?

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlCK1YlWsgCTxmDSEExCxC0xRAyRSCMkcRMwaQYmTZFIIyRxCwyTzZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", Int64.Type}}),
    #"Grouped by 0" = Table.Group(#"Changed Type", "Data", {"Grp", each _}, 0, (x,y) => Number.From(y=0)),
    #"Added Index" = Table.AddIndexColumn(Table.RemoveColumns(#"Grouped by 0", "Data"), "Index", 1, 1, Int64.Type),
    #"Expanded Grp" = Table.ExpandTableColumn(#"Added Index", "Grp", {"Data"}, {"Data"})
in
    #"Expanded Grp"

     

    Also, Excel formula is concise enough,

     

2 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    4 years ago

    What's the point constructing a recursive func whereas there's simple native Table.Group() available?

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlCK1YlWsgCTxmDSEExCxC0xRAyRSCMkcRMwaQYmTZFIIyRxCwyTzZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", Int64.Type}}),
    #"Grouped by 0" = Table.Group(#"Changed Type", "Data", {"Grp", each _}, 0, (x,y) => Number.From(y=0)),
    #"Added Index" = Table.AddIndexColumn(Table.RemoveColumns(#"Grouped by 0", "Data"), "Index", 1, 1, Int64.Type),
    #"Expanded Grp" = Table.ExpandTableColumn(#"Added Index", "Grp", {"Data"}, {"Data"})
in
    #"Expanded Grp"

     

    Also, Excel formula is concise enough,