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.
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})
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.