Group table elements with a maximum number of elements per group

Hi MatLcq ,

 

I think that Anonymous 's theory is correct, but I think you'll get errors on the adding index step and won't have dynamic BitIndex generation when your contiguous list of BIT variables exceeds 16 in a row.

 

Paste this over the default code in a new blank query to see how I recommend to achieve this:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZKxisJAFEV/JaS2yJsZk0wrWli4C7uyIsHCwlIEsdC/N++x7LzZe6uQwyHcOZlpanevn/Nd2kV7+Pxaz4/v2/XSPC7PR6OwaU+LXyfMr6vtvlKUdUWJRFEmRUlEURaKsiSKsliUnijKUlEGoihbFmUkirK+KHl+3X7USq67SEc+Y9AdW4RJUh9cWGKD7ujCIht0hxeW2aA7vrDQBl0AYakNDk5isQ26yyEst0HfKTMp151CR/6KQX9dWXGDblOglzrUmwIrbtBvsuLH/aa2lJoUSuw/1CMaEI2IMqDYIRJEAVFElBDh+ojrI66PuD7i+oTrE65PuD7h+vRv/ekN", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Data Type" = _t, Description = _t, BitIndex = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Data Type", type text}, {"Description", type text}, {"BitIndex", Int64.Type}}),
    groupRows = Table.Group(chgTypes, {"Data Type"}, {{"data", each _, type table [Name=nullable text, Data Type=nullable text, Description=nullable text, BitIndex=nullable number]}}, GroupKind.Local),
    addIndex = Table.TransformColumns(groupRows, {"data", (i) => Table.AddIndexColumn(i, "Index", 0, 1)}),
    addBitIndex = Table.TransformColumns(addIndex, {"data", (i) => Table.AddColumn(i, "BitIndexCalc", each if [Data Type] = "BIT" then Number.Mod([Index], 8 ) else null)}),
    expandData = Table.ExpandTableColumn(addBitIndex, "data", {"Name", "Description", "BitIndex", "BitIndexCalc"}, {"Name", "Description", "BitIndex", "BitIndexCalc"})
in
    expandData

 

 

 

Summary:

1) Group on [Data Type] with GroupKind.Local argument

2) Add a zero-base index to the nested tables

3) Add [BitIndexCalc] to the nested tables using Number.Mod([index], 8 ) to ensure your bit index always resets to zero after every 8 contiguous entries

4) Expand the columns you need

 

Note that my [BitIndexCalc] field doesn't match your original [BitIndex] field. Not sure if your original field was achieving some unstated objective, but the new field will always assign 8 contiguous bits to a byte wth a zero base.

 

Pete