Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
Thanks @BA_Pete, the Number.Mod was the kind of code I was looking for (and I should've though about it... -_-).
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 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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.