cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MatLcq
New Member

Group table elements with a maximum number of elements per group

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

 

NameData TypeDescription
MyVar1WORDSome text 1
MyVar2BITSome text 2
MyVar3BITSome text 3
MyVar4BITSome text 4
MyVar5BITSome text 5
MyVar6BITSome text 6
MyVar7BITSome text 7
MyVar8BITSome text 8
MyVar9INTSome text 9
MyVar10BITSome text 10
MyVar11BITSome text 11
MyVar12BITSome text 12
MyVar13BITSome text 13
MyVar14BITSome text 14
MyVar15BITSome text 15
MyVar16BITSome text 16
MyVar17BITSome text 17
MyVar18BITSome text 18
MyVar19BITSome text 19
MyVar20INTSome text 20
MyVar21BITSome text 21
MyVar22BITSome text 22
MyVar23BITSome text 23
MyVar24BYTESome 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.

 

NameData TypeDescriptionBitIndex
MyVar1WORDSome text 1 
MyVar2BITSome text 20
MyVar3BITSome text 31
MyVar4BITSome text 42
MyVar5BITSome text 53
MyVar6BITSome text 64
MyVar7BITSome text 75
MyVar8BITSome text 86
MyVar9INTSome text 9 
MyVar10BITSome text 101
MyVar11BITSome text 112
MyVar12BITSome text 123
MyVar13BITSome text 134
MyVar14BITSome text 145
MyVar15BITSome text 156
MyVar16BITSome text 167
MyVar17BITSome text 170
MyVar18BITSome text 181
MyVar19BITSome text 192
MyVar20INTSome text 20 
MyVar21BITSome text 210
MyVar22BITSome text 221
MyVar23BITSome text 232
MyVar24BYTESome 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

 
 
 
 
1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

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

View solution in original post

4 REPLIES 4
BA_Pete
Super User
Super User

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

View solution in original post

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

 
watkinnc
Super User
Super User

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

 


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
MatLcq
New Member

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

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors