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.
Hi, I'm struggling to create a redundant index column and another index column which references the redundant index column.
I've grouped a set of data using the Group By function and within each Group By table I need the following two index columns.
Example>
Index1, Index2
0,0
0,1
0,2
0,3
0,4
1,0
1,1
1,2
1,3
1,4
2,0
2,1
2,2
2,3
2,4
Note: The first index columns actually has 10 repeats but I used 5 to shorten this example.
Thanks...
Solved! Go to Solution.
Hi, @roncruiser
The complete code, I've changed it, you just need to follow the picture below to modify the parameters to achieve your desired effect.
// output
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
chType = Table.TransformColumnTypes(Source,{{"Delay", type number}}),
fx = (tbl as table, Coarse_start as number, Fine_start as number, Fine_end as number)=>
let
sortedTbl = Table.Sort(tbl, {"Delay", 0}),
rows = List.Buffer(Table.ToRows(sortedTbl)),
n = List.Count(rows),
gen = List.Generate(
()=>{0, {}, 0},//{counter, {new_list}, delay_counter}
each _{0}<=n,
each let count = Fine_end+1-Fine_start,
Index1 = _{0},
Coarse = Number.IntegerDivide(_{0}, count)+Coarse_start,
Fine = Number.Mod(_{0}, count)+Fine_start
in {_{0}+1, {Index1, Coarse, Fine}, _{0}},
each List.InsertRange(rows{_{2}}, 4, _{1})
),
toTbl = Table.FromRows(
List.Skip(gen),
List.InsertRange(
Table.ColumnNames(sortedTbl),
4,
{"Index1", "Coarse", "Fine"}
)
)
in
toTbl,
group = Table.Group(chType, "Ref", {"t", each fx(_, 3, 4, 11)})[t],
result = Table.Combine(group)
in
result
That is really odd @roncruiser - I used Imke's code in both Power BI (August 2020) and Excel from Office 365 (version 2008) and it works fine. I cannot see any new things in her code that an older version might break. What is your Excel version. "Pro Plus" doesn't tell a version, but a licensing level. Is this Excel 2010, 2013, 2016, 2019, or Excel from office 365, which should, at most, be 6 months old if on a deferred channel.
Also, paste this code in Power BI and see if it works there just to make sure you are doing that part correctly.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThat is old, but not sure how old. The better version to use is this:
2008 refers to 2020, August. You might have 1909, which would be Sept 2019, or whatever. I don't know how read the build numbers and convert to when published. But the Version is easy.
Did it work in Power BI Desktop?
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThe code did work in Power BI
More information about the Excel version I run.
Unfortunately I have no control of the version I can run at work. And Power BI is not an option with they way we display the data.
With everyone's help, I think your problem has been solved, so if any of the solutions meet your expectations, please mark them as solutions.
Thanks for the outstanding support. Apparently, the solution to the problem was only perplexing to me.
Im a hack at M code. I'm successful enough to keep me in the M code world, but still require immense help as I use
M code only once in awhile because its built into Excel. I'm not immersed in M code, but bang on it to get what I need. The solution is quite elegant and I'm still trying to understand it. Every time I come to this communiny I learn enough to solidy my position for M code as a viable tool to assist my group.
We use PowerQuery to develop an Excel visualization tool. For a single
visualization, the output is up to 15M lines long. For mutliple visualization it could run into the hundreds for millions of lines long.
The only drawback, I'm finding with the Excel Power Query solution is that it requires an Idle Excel while it processes the data.
I was hoping there was a way to tier the accepted solutions. There are two accepted solution:
@ziying35: The Implemented solution. It's been implemented into the visualization tool.
@ImkeF: A working solution.
Again, THANK YOU.
Glad you have a solution @roncruiser - you can mark multiple answers as solutions.
As for Excel needing to be idle, yeah, when it is processing large volumes of data itself - i.e. data that cannot be folded back to a server for processing, you just need to hit refresh and go get a cup of coffee or something. Two things that will greatly speed things up.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting
I'm running a 16GB company issued laptop. Will more RAM help with speed to locally process data on the laptop?
With working and running Excel locally from home, and processing individual stored files on some far-away (non-database) server any small incremental gain in processing time will help.
You'd have to launch Task Manager and watch the mashup engine's memory consumption. If Excel plus the Microsoft.Mashup engine are taking 14GB or more combined, then I'd say more would help as Windows is proably limititing RAM to that and using the hard drive as swap space.
Note that depending on your queries, you may have multiple mashup engines running.
Unfortunately in Excel, you cannot disable parallel processing like you can in Power BI desktop. In a memory constrained environment, having 4 queries run taking up 4GB each will cause your computer to run slower than running them one at a time as the system will have to use swap space to handle the 16GB needed by Excel plus the 2GB or so that Windows needs just to exist.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI see the problem:
#"Sorted Rows" = Table.Buffer(Table.Sort(myPartition,{{"Delay", Order.Ascending}})),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type), <----
#"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Coarse", each Number.IntegerDivide([Index], 10), Int64.Type),
#"Inserted Modulo" = Table.AddColumn(#"Inserted Integer-Division", "Fine", each Number.Mod([Index], 10), type number)
Get rid of the ", Int64.Type" part of the index. That is relatively new, and causing your query to crash. So do this:
#"Sorted Rows" = Table.Buffer(Table.Sort(myPartition,{{"Delay", Order.Ascending}})),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
#"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Coarse", each Number.IntegerDivide([Index], 10), Int64.Type),
#"Inserted Modulo" = Table.AddColumn(#"Inserted Integer-Division", "Fine", each Number.Mod([Index], 10), type number)
Making indexes an integer is best practice, and Power BI and Excel were updated to do this automatically with this 5th parameter. It causes old versions to crash.
It doesn't matter in this case though because you need to set all data types at the end of Imke's code anyway, so make them integer/whole numbers there.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportingthanks @edhans
for stepping in here and clearing things up!
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
This is tricky and I keep running into dead ends.
I'm hoping I can end up with a solution. So I came here to Power BI community
for fresh new perspective from some of the experts.
Thanks so much...
Hi @roncruiser ,
please check out this code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddtBbtxGEAXQu3idAKyuZje5laO5hJH7XyMyWyNrWC8LwwnxIwR5tkf/p/jr14/tx18/+naObfv9V18/Pv/290///vXrR5TU34FYq7GGWNZYItZrrCO219iO2KixgdissYnYUWMHYmeNnfrPWxX+DjoIQhIBipBFACOkEeAIeQRAQiIBkpBJACWkEmAJuQRgQjINMk0yDTKNv0f0m0QyDTJNMg0yTTINMk0yDTJNMg0yTTINMk0yDTJNMgmZlExCJiWTkEn++aU/wCSTkEnJJGRSMgmZlExCJiWTkEnJJGRSMh0yXTIdMl0yHTJdMh0ynZ8t+nCRTIdMl0yHTJdMh0yXTIdMl0yHTJfMDpldMjtkdsnskNkls0Nml8wOmZ2f+/rgl8wOmV0yO2R2yeyQ2SWzQ2aXzIDMkMyAzJDMgMyQzIDMkMyAzJDMgMzg92T6pkwyAzJDMgMyQzIDMkMyEzJTMhMyUzITMlMyEzJTMhMyUzITMlMyEzKT3y/rG2bJTMhMyUzITMkckLmebXELQuZ6VoKQuZ4d9yBkrmdv5y0ImevZ+XYLQuZ69va4BSFzPXt7O16DkLmePR63oMrMsYLxEoTM9ewjuH8PnpC5npUgZK5nJQiZ61kJQuZ69hH850l+BSFzPXsGz2cQMtezx+N8DULmevbxFc+XIGTO+Sf49hWEzHn8+Xd8PJ5BFc0TwfXruFTNjdGKw6q5VRs2za3SsGhuVYY9c6swrJlbdWHL3CoLS+ZWVdgxt4rC8o/2/z/lHyAUQft3+Uf7d/lH+3f5R/t3+Uf7d/lH+3f5R/t3+Uf7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7fy3//RmsMq/l/ytYZV7L/1ewyryW//EM4lvmF5mPXyIrWGVey//H11/BKvNa/p9BtP/ju0yf75/BKnN8l+mPZ7DKHN9k+ng8PoNV5vgu8/MrWGWObzL9/StYZVat7f0WrDLXo+3n9QE2/wSrzPXo4yvegmgzx5+v+PgTrDKr1q6v+Hg8g2j/16P3n/v7y1dE+1/l/+d8f/2KVWaV/xKsMqv8v8/Ha7DKrPJfglVmlf8SrDKr/JdglVnlvwSrzGf5f9yCKJongm2rMs/uf0+iam7BJLrm1phE2dySSbTNrTOJurntTKJvboNJFM5tMonGuYmoYQtY+0BJag0IGnEOoJH2gKCRBoGgkRaBoJEmgaCRNoGgkUaBoJFWgaCRZoGgEXaBtRXUJIwajbAMrLWgJmHUaIRtYO0FNQmjRiOsA2sxqEkYNRphH1ibQU3CqNEIC8FaDWoSRkkjbARrN6hJDWs0wkqwloOahFHSCDvB2g5qEkZJIywFaz2oSRgljbAVrP2gJmHUaYS1YC0INQmjTiPsBdFphMFgjQg1CaNOI0wGa0aoSRh1GmE0WENCSWI1WEtCTcJopxF2g7Ul1CSMdhphOVhrQk1qoqYRtoO1J9QkjHYaYT1Yi0JNwminEfaDtSnUJIwGjbAgrFWhJmE0aIQNYe0KNQmjQSOsCGtZqEkYDRphR1jbQk3CaNAIS8JaF2oSRpNG2BLWvlCTMJo0wpqwFoaahNGkEfaEtTHUpP5nD42wKKyVoSZhNGmETWHtDDUJo4NGWBXW0lCTMDpohF1hbQ01CaODRlgW1tpQkzA6aIRtIQ4aYVyIg0ZYF+KkEeaFOGmEfSFOGmFgiJNGWBjipBEmhjhphI0hThphZIiTRlgZgjNDw8wQ3BkSO0PjzpDYGRp3htSVAXeGxM7QuDMkdobGnSGxMzTuDImdoXFnSOwMjTtDYmdo3BkSO0PjzpDYGRp3hsTO0LgzJHaGxp0hsTM07gyJnaFxZ0jsDI07Q2JnaNwZEjtD486Q2Bkad4bEztC4M6TuD7gzpA4QuDMkLxBopBME7gypGwTuDKkjBO4MqSsE7gypMwTuDKk7BO4MqUME7gyJnaFxZ0jsDI07Q2JnaNwZEjtD486Q2Bkad4bEztC4MyR2hsadIbEzNO4MiZ2hcWdI7AyNO0NiZ2jcGRI7Q+POkNgZGneGvO0M68253z8+/8nfP61gr0G+Z5e3mWEl+arKbWZYSb+rMpHkyyq3mWEl+bbKbWZYSb6ucpsZVpLvq9xmhpXkCyu3mWEl+S7RDiK/gZc7kai0S4mHeLmLiad4ucuJx3i5C4rneLlLigd5OUTFk7wcsuJRXg5h8Swvh7R4mJdDWjzNy8HfU9Qa0uJ5Xg5p8UAvh7R4opdDWjzSyyktnunllBYP9XJKy+/pTWn5Tb0pLb+rN6Xlt/Um/wik1pSW39ib0vI7e1NafmvvkNb/vLcnLb+5d0iL53t5SIsHfHlIiyd8eUiLR3x5SItnfHnwE4tah7R4ypeHtHjMl6e0eM6Xp7R40JentHjSl6e0eNSXp7R41pentHjYl6e0eNqXp7R43Jcnv8Gg1iktHvj1TVo88eubtHjk1zdp8cyvb9LioV/fpMVTv75Ji8d+fZMWz/36Ji0e/PVNWjz56xu/H6RWSItnfz2kxcO/HtLi6V8PafH4r4e0eP7XQ1o8AOwhLb7/10NafAOwh7T4DmAPafEtwN6kxfcAe5MW3wTsTVp8F7A3aR3UatLi+4C9SYtvBPYmrevTtEaldX2aHm/3qLTWe4ElKi2+GdhTWten6Uc0XqNsW3o7sKe0Pkf8c3+NSutzxb9HpfU543+L/vsf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Iteration = _t, Frequency = _t, Ref = _t, Delay = _t, Capture1 = _t, Capture2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Iteration", Int64.Type}, {"Frequency", Int64.Type}, {"Ref", Int64.Type}, {"Delay", Int64.Type}, {"Capture1", type text}, {"Capture2", type text}}),
fnTransformation = (myPartition as table) => let
#"Sorted Rows" = Table.Buffer(Table.Sort(myPartition,{{"Delay", Order.Ascending}})),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
#"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Coarse", each Number.IntegerDivide([Index], 10), Int64.Type),
#"Inserted Modulo" = Table.AddColumn(#"Inserted Integer-Division", "Fine", each Number.Mod([Index], 10), type number)
in
#"Inserted Modulo",
#"Grouped Rows" = Table.Group(#"Changed Type", {"Ref"}, {{"All", each _, type table [Iteration=nullable number, Frequency=nullable number, Ref=nullable number, Delay=nullable number, Capture1=nullable text, Capture2=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "ApplyFunction", each fnTransformation([All])),
#"Expanded ApplyFunction" = Table.ExpandTableColumn(#"Added Custom", "ApplyFunction", {"Iteration", "Frequency", "Delay", "Capture1", "Capture2", "Index", "Coarse", "Fine"}, {"Iteration", "Frequency", "Delay", "Capture1", "Capture2", "Index", "Coarse", "Fine"})
in
#"Expanded ApplyFunction"
I've used the techniques from my previous post:
The "Coarse"-column is created using the IntegerDivide-Function and the "Fine"-column using the Modulo.
They are hardcoded to 10, but this could be turned into a variable once you're able to specify how you're going to "feed" the variing numbers into this solution.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Or try this:
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
chType = Table.TransformColumnTypes(Source,{{"Delay", type number}}),
fx = (tbl)=>
let
sortedTbl = Table.Sort(tbl, {"Delay", 0}),
rows = List.Buffer(Table.ToRows(sortedTbl)),
n = List.Count(rows),
gen = List.Generate(
()=>{0,{}},
each _{0}<=n,
each {
_{0}+1,
let idx_lst={_{0}, Number.IntegerDivide(_{0},10), Number.Mod(_{0}, 10)}
in List.InsertRange(rows{_{0}}, 4, idx_lst)
},
each _{1}
),
toTbl = Table.FromRows(List.Skip(gen), List.InsertRange(Table.ColumnNames(sortedTbl), 4, {"Index1", "Coarse", "Fine"}))
in
toTbl,
group = Table.Group(chType, "Ref", {"t", fx})[t],
result = Table.Combine(group)
in
result
I've re-provided two solutions, both of which are similar in terms of code efficiency, and the last one has the table fields sorted in the order you expect them to be.
If my code solves your problem, mark it as a solution
@edhans Sure. Thank you
Let me see if I can explain with the following...
This is my source data:
I then transform the data using the Group By feature:
After Group By:
Each Value in the "Ref" column now has it's own table.
Here's what I can't figure out...
Index1,Index2,Index3
0,0,0
1,0,1
2,0,2
3,0,3
4,0,4
5,0,5
6,0,6
7,0,7
8,0,8
9,0,9
10,1,0
11,1,1
12,1,2
13,1,3
14,1,4
15,1,5
16,1,6
17,1,7
18,1,8
19,1,9
.... until the end of the column inside each table.
I'll see if I can upload a sample set of data.
-correction made- 082420 @1754
I have seen, even without having read all the messages, that the problem has found several solutions.
I don't intend to add solutions, also because I don't read the specs right.
I just wanted to expose a new (?) point of view on this sequence ..
Index1,Index2,Index3
0,0,0
1,0,1
2,0,2
3,0,3
4,0,4
5,0,5
6,0,6
7,0,7
....
....
let
nrow=Table.RowCount(Table2),
Query1 = Table.FromColumns({{0..nrow}},{"L1"}),
#"Duplicated Column" = Table.AddColumn(Query1,"L2",each Text.PadStart(Text.End(Text.From(_[L1]),2),2,"0")),
#"Split Column by Position" = Table.SplitColumn(#"Duplicated Column", "L2", Splitter.SplitTextByRepeatedLengths(1), {"L2.1", "L2.2"})
in
#"Split Column by Position"
@roncruiser - Hmm, not sure of the Power Query solution for this, but maybe @ImkeF or @edhans knows. I did do something similar in DAX called Cthulhu:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/m-p/509739#M211
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.