Number Rows by Group and Order

[Sam7]

Hi

I Power Query, how do I number each row of data, so each group is the same number, but I only want the numbering to go upto 5 then start again?

Cheers

[Ahmedx]

pls try this code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqqqqUorVwaQdnZzBtKtnMArt7OiCQoe4QtR5OTphpT0cwZRvgD8KHRmETHn6OAKVxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Group"}, {{"Count", each _, type table [Group=nullable text]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Index", each if  Number.Mod(_, 5)=0 then 5 else Number.Mod(_, 5) , type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Calculated Modulo",{"Group"}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Removed Columns", "Count", {"Group"}, {"Group"})
in
    #"Expanded Count"

[lbendlin]

Use GroupKind.Local and Modulus.

If you like more help please provide the sample data in a usable format, not as a screenshot.