Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
bbrimberry
New Member

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

 

 

 

 

excel picture.PNG

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

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"

Screenshot 2021-10-03 041805.png

 

Also, Excel formula is concise enough,

Screenshot 2021-10-03 041927.png

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

2 REPLIES 2
CNENFRNL
Community Champion
Community Champion

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"

Screenshot 2021-10-03 041805.png

 

Also, Excel formula is concise enough,

Screenshot 2021-10-03 041927.png

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

thank you!

this worked as I intended 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors