Forum Discussion
Post ProdigyAdjusting positions in a list
Hi, can anyone come up with a way to adjust posiotns of change in a list, so they can be used with
List.accumulate to insert rows, i have use List.TransformMany, ; so to insert at each chaange in value ;
[
alist = {"a", "a", "a", "b", "b", "b", "b", "c", "c", "d", "d", "d", "e"},
insertN = 1,
tf = List.Transform({0 .. List.Count(alist) - 2}, (x) => alist{x} = alist{x + 1}),
pos = List.PositionOf(tf, false, Occurrence.All),
adjust = List.TransformMany(
{0 .. List.Count(pos) - 1},
(x) => {pos{x} + 1},
(x, y) => y + (x * insertN)
),
rslt = List.Accumulate(adjust, alist, (s, c) => List.InsertRange(s, c, {null}))
]
I know there are lots of way to apprach the inserting problem, but it's getting the correct positions for this particular
method I'm interested in not differeent methods of inserting at change, I have tried List.Generate, which I think should
be do -able ,but have not got it to work yet.
Hi Dicken,
Your logic is correct: to use List.Accumulate for inserting rows, you need to calculate the adjusted positions, taking into account the shifts caused by previous insertions. In your current approach, List.PositionOf returns the original positions without considering these cumulative shifts.
Please try stwitch you code for these:let alist = {"a", "a", "a", "b", "b", "b", "b", "c", "c", "d", "d", "d", "e"}, insertN = 1, result = List.Generate( () => [i = 0, prev = alist{0}, acc = {}], each [i] < List.Count(alist), each if [i] = 0 then [i = [i] + 1, prev = alist{0}, acc = {[prev]}] else if alist{[i]} <> [prev] then [i = [i] + 1, prev = alist{[i]}, acc = List.Combine({[acc], List.Repeat({null}, insertN), {alist{[i]}}})] else [i = [i] + 1, prev = [prev], acc = [acc] & {alist{[i]}}], each [acc] ), rslt = List.Last(result) in rsltI testet, let me know if thats it are you expectig for:
If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.
Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.
Don't forget the Fifth Element!
Table.Group: Exploring the 5th element in Power BI and Power Query –
That can provide a much more elegant solution.
obligatory shout-out to ImkeF
9 Replies
- lbendlin
Super User
Don't forget the Fifth Element!
Table.Group: Exploring the 5th element in Power BI and Power Query –
That can provide a much more elegant solution.
obligatory shout-out to ImkeF
DickenI have not been able to go through these, will do and get back to you tomorow.
thanks for the response to both of you, i like the look of the zanqueta approach, and yes
that is the problem true / false, needs adjustment, and then adjsuting again to allow for insertion.
I will work through, but think i'll stick to my method, it's just simpler, if not better.- Dicken
re table group, 5th element , not sure how this helps, for example if you do ;
= Table.Group( Source, {"Item"} ,
{{"N", each let t = _ in
Table.InsertRows( t, Table.RowCount( t ) ,
{ [Item = null ] } ) , (x,y)=>
how don't see how the comparer can be used to prevent adding rows to the final tablle , which
is why i tend not to use it. Unless you meant something completely different ?- lbendlin
If you don't like that approach you can go for something simpler
let Source = {"a", "a", "a", "b", "b", "b", "b", "c", "c", "d", "d", "d", "e","a","a","b"}, #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1, Int64.Type), #"Grouped Rows" = Table.Group(#"Added Index", {"Column1"}, {{"Rows", each _, type table [Column1=text, Index=number]}, {"MinIndex", each List.Min([Index]), type number}},GroupKind.Local), #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Index"}, {"Index"}) in #"Expanded Rows"
- Zanqueta
Hi Dicken,
Your logic is correct: to use List.Accumulate for inserting rows, you need to calculate the adjusted positions, taking into account the shifts caused by previous insertions. In your current approach, List.PositionOf returns the original positions without considering these cumulative shifts.
Please try stwitch you code for these:let alist = {"a", "a", "a", "b", "b", "b", "b", "c", "c", "d", "d", "d", "e"}, insertN = 1, result = List.Generate( () => [i = 0, prev = alist{0}, acc = {}], each [i] < List.Count(alist), each if [i] = 0 then [i = [i] + 1, prev = alist{0}, acc = {[prev]}] else if alist{[i]} <> [prev] then [i = [i] + 1, prev = alist{[i]}, acc = List.Combine({[acc], List.Repeat({null}, insertN), {alist{[i]}}})] else [i = [i] + 1, prev = [prev], acc = [acc] & {alist{[i]}}], each [acc] ), rslt = List.Last(result) in rsltI testet, let me know if thats it are you expectig for:
If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.
Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.
- Dicken
can you check i accepted the generate which is what I was looking for. thanks very much very clever
I will have to spend a whike going through it. - Rufyda
I'm glad you found a solution! We're here in the community to support each other.
Regards,
Rufyda Rahma | MIE
