Group table elements with a maximum number of elements per group

[MatLcq]

Hello,

I'm working with large tables of variables. Each one of these variable has a NAME, a DATA TYPE and a DESCRIPTION.

DATA TYPE can be one of the usual programming data types: BIT, BYTE, WORD, ...

Name	Data Type	Description
MyVar1	WORD	Some text 1
MyVar2	BIT	Some text 2
MyVar3	BIT	Some text 3
MyVar4	BIT	Some text 4
MyVar5	BIT	Some text 5
MyVar6	BIT	Some text 6
MyVar7	BIT	Some text 7
MyVar8	BIT	Some text 8
MyVar9	INT	Some text 9
MyVar10	BIT	Some text 10
MyVar11	BIT	Some text 11
MyVar12	BIT	Some text 12
MyVar13	BIT	Some text 13
MyVar14	BIT	Some text 14
MyVar15	BIT	Some text 15
MyVar16	BIT	Some text 16
MyVar17	BIT	Some text 17
MyVar18	BIT	Some text 18
MyVar19	BIT	Some text 19
MyVar20	INT	Some text 20
MyVar21	BIT	Some text 21
MyVar22	BIT	Some text 22
MyVar23	BIT	Some text 23
MyVar24	BYTE	Some text 24

My goal is to add a column named BIT NUMBER that indicates - in case the variable is a BIT - its index number in a virtual BYTE. All the other data types would have a NULL value in that column. The trick is that the BIT index can be of {0..7} values only, since there a maximum of 7 bits in a byte.

Name	Data Type	Description	BitIndex
MyVar1	WORD	Some text 1
MyVar2	BIT	Some text 2	0
MyVar3	BIT	Some text 3	1
MyVar4	BIT	Some text 4	2
MyVar5	BIT	Some text 5	3
MyVar6	BIT	Some text 6	4
MyVar7	BIT	Some text 7	5
MyVar8	BIT	Some text 8	6
MyVar9	INT	Some text 9
MyVar10	BIT	Some text 10	1
MyVar11	BIT	Some text 11	2
MyVar12	BIT	Some text 12	3
MyVar13	BIT	Some text 13	4
MyVar14	BIT	Some text 14	5
MyVar15	BIT	Some text 15	6
MyVar16	BIT	Some text 16	7
MyVar17	BIT	Some text 17	0
MyVar18	BIT	Some text 18	1
MyVar19	BIT	Some text 19	2
MyVar20	INT	Some text 20
MyVar21	BIT	Some text 21	0
MyVar22	BIT	Some text 22	1
MyVar23	BIT	Some text 23	2
MyVar24	BYTE	Some text 24

Is there a way to achieve that elegantly?

So far, here is my solution, but it's not pretty:

Thank for any tips

[BA_Pete · Accepted solution]

Hi @MatLcq ,

I think that @watkinnc '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

[MatLcq]

Thanks @BA_Pete, the Number.Mod was the kind of code I was looking for (and I should've though about it... -_-).

[watkinnc]

You could first group on Data type, but adding the GroupKind.Local parameter at the end of the function. This will group contiguous values only. Be sure to add an All Rows aggregation. Name it "Details".

Then add an index column to each table:

Table.TransformColumns(PriorStepName, {{"Details", each Table.AddIndexColumn(_, 1)}})

Then add another column for the byte number math:

Table.TransformColumns(PriorStepName, {{"Details", each Table.AddColumn(_, "Byte No", each if [Index] < 8 then [Index] else if [Index] < 15 then [Index] - 7 else [Index] - 14}}))

Then just expand the columns.

--Nate

[MatLcq]

I'm sorry but I don't understand how to paste Power Query code in the message editor. It throws an error no matter if I'm using HTML formatted code or just plain text...

let // function declaration fnBoolIndex = (pTable as table, pIndex as number, pCount as number) => if try pTable[DataType]{pIndex-1} = "BIT" otherwise false then if pCount < 7 then @fnBoolIndex(pTable, pIndex - 1, pCount + 1) + 1 else @fnBoolIndex(pTable, pIndex - 1, 0) - 7 else 0, // data transformation sGetSource = Excel.CurrentWorkbook(){[Name="MyData"]}[Content], sAddIndex = Table.AddIndexColumn(sGetSource, "Index"), sBitIndex = Table.AddColumn(sAddIndex, "BitNumber", (_) => if _[DataType] = "BIT" then fnBoolIndex(sAddIndex, _[Index], 0) else null, Number.Type) in sBitIndex