Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm thinking how one would go about doing this in PQ.
Input : arr1[] = {1, 2, 3, 4, 5, 6} arr2[] = {11, 22, 33, 44} Output: {1, 11, 2, 22, 3, 33, 4, 44, 5, 6} Input : arr1[] = {1, 2, 3, 4, 5, 6, 7, 8} arr2[] = {11, 22, 33, 44} Output: {1, 11, 2, 22, 3, 33, 4, 44, 5, 6, 7, 8}
I was thinking about using indexes with a different starting point
Table1: starting at 1 with a 2 increment step.
Table2: starting at 2 with a 2 increment step.
Append tables, order by index column and it should be ordered correctly.
Perhaps you have a more efficient suggestion?
KR,
João
Solved! Go to Solution.
Assuming the arrays are list objects in the Query editor, you can use this function:
List.Select(List.Combine(List.Zip({Array1, Array2})), each _ <> null)
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
Assuming the arrays are list objects in the Query editor, you can use this function:
List.Select(List.Combine(List.Zip({Array1, Array2})), each _ <> null)
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
Output: {1, 11, 2, 22, 3, 33, 4, 44, 5, 6}
@ImkeF How to get each combination into separate row ?
ID | value |
1 | 1,11 |
2 | 2,22 |
3 | 3,33 |
you could use the tool Merge Columns, after having selected the involved columns:
then choice the separator you like:
PS
I intruded into the discussion to show a different approach, but only for fun.
I just wanted to experiment with recursion and I wrote this @Anonymous.
let
interleave=(a1,a2,pos) =>
let
la1=List.Count(a1),
la2=List.Count(a2),
gl=if la1 >= la2 then a1 else a2,
ll=if la1 < la2 then a1 else a2,
ins_a1=List.InsertRange(gl,2*pos+1,{ll{pos}}),
p=pos+1
in
if p < List.Min({la1,la2}) then @interleave(ins_a1,ll,p) else ins_a1
in interleave
if a generalized List.InsertRange function, such as the following InsertRanges (which is only a function toy), were available,
let
InsertRanges=(lst,positions,ranges, optional p) =>
let
cur=if p=null then 0 else p,
pos=positions{cur},range=if Value.Type(ranges{cur})= type list then ranges{cur} else {ranges{cur}},
inserted=List.InsertRange(lst,pos,range),
next=cur+1
in
if next < List.Count(positions) then @InsertRanges(inserted,positions,ranges,next) else inserted
in InsertRanges
the interleave problem could be solved with a simple call.
= InsertRanges({1,2,3,4,5,6,7,8}, {1,3,5,7}, {11,22,33,44})