Group Data by number of rows

Question

Hello my PBI friends&nbsp;Odd question, here. I have table that includes the following data, sorting State+County in ascending order:&nbsp;StateCountyState+CountyIndexArkansasBradleyArkansas Bradley1ArkansasCalhounArkansas Calhoun2ArkansasChicotArkansas Chicot3ArkansasClarkArkansas&nbsp;Clark4ArkansasClayArkansas&nbsp;Clay5ArkansasCraigheadArkansas&nbsp;Craighead6ArkansasCrossArkansas&nbsp;Cross7ArkansasDeshaArkansas&nbsp;Desha8ArkansasGreeneArkansas&nbsp;Greene9ArkansasHempsteadArkansas&nbsp;Hempstead10ArkansasIzardArkansas&nbsp;Izard11ArkansasJeffersonArkansas&nbsp;Jefferson12ArkansasLeeArkansas&nbsp;Lee13ArkansasLincolnArkansas&nbsp;Lincoln14ArkansasMillerArkansas&nbsp;Miller15&nbsp;I want to add a new "Group" column and group them so that the first five (Index 1 - 5) are group "1", second five (Index 6 - 10) are group "2" and so forth, like this:&nbsp;StateCountyState+CountyIndexGroupArkansasBradleyArkansas Bradley11ArkansasCalhounArkansas Calhoun21ArkansasChicotArkansas Chicot31ArkansasClarkArkansas&nbsp;Clark41ArkansasClayArkansas&nbsp;Clay51ArkansasCraigheadArkansas&nbsp;Craighead62ArkansasCrossArkansas&nbsp;Cross72ArkansasDeshaArkansas&nbsp;Desha82ArkansasGreeneArkansas&nbsp;Greene92ArkansasHempsteadArkansas&nbsp;Hempstead102ArkansasIzardArkansas&nbsp;Izard113ArkansasJeffersonArkansas&nbsp;Jefferson123ArkansasLeeArkansas&nbsp;Lee133ArkansasLincolnArkansas&nbsp;Lincoln143ArkansasMillerArkansas&nbsp;Miller153&nbsp;I tried a few different things but I could not figure out how to get it the way I need it.&nbsp;&nbsp;Any ideas?&nbsp;Thank you!!

fowmy · Accepted Answer

Anonymous&nbsp;Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdHJDsIgEIDhVzFc9SDuHrUmLtGTx8bDxE6FiGAAE/XprQsti7fO3w9CMnlO9hYskg7J1E3aR/XxCe16XMsC7+TQyclMn0EaMFWcaygEvn+72GoSjXUGglXX+bpJvUQzflQ2wK70EytAnwP6C4M/8hHB9zxMnAZ+YghFgL04Sk8oY0L9DeNYLtAw8KULk1guNaJEn9ZlGtsVXq7GRi/2I+3GR9ZP0AF3gSa722BZojYq2J4fabLALQYv/4402d2Wy6MSwb1NoskCd1wI1L6uC622eHgB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Index", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index.1", 0, 1, Int64.Type),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index.1", each Number.IntegerDivide(_, 5), Int64.Type}}),
    #"Added to Column" = Table.TransformColumns(#"Integer-Divided Column", {{"Index.1", each _ + 1, type number}})
in
    #"Added to Column"

Forum Discussion

Group Data by number of rows

2 Replies

Related content

Fabric & Azure Data User Group Program Requirements & Benefits

Community User Group Experience

Sum of Number in group

User Group Leader Get Started Guide

FAQ on the Fabric User Groups

State	County	State+County	Index
Arkansas	Bradley	Arkansas Bradley	1
Arkansas	Calhoun	Arkansas Calhoun	2
Arkansas	Chicot	Arkansas Chicot	3
Arkansas	Clark	Arkansas Clark	4
Arkansas	Clay	Arkansas Clay	5
Arkansas	Craighead	Arkansas Craighead	6
Arkansas	Cross	Arkansas Cross	7
Arkansas	Desha	Arkansas Desha	8
Arkansas	Greene	Arkansas Greene	9
Arkansas	Hempstead	Arkansas Hempstead	10
Arkansas	Izard	Arkansas Izard	11
Arkansas	Jefferson	Arkansas Jefferson	12
Arkansas	Lee	Arkansas Lee	13
Arkansas	Lincoln	Arkansas Lincoln	14
Arkansas	Miller	Arkansas Miller	15