cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Combine two arrays, alternating their elements

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

1 ACCEPTED SOLUTION
Super User II
Super User II

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

View solution in original post

4 REPLIES 4
Super User II
Super User II

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

View solution in original post

Output: {1, 11, 2, 22, 3, 33, 4, 44, 5, 6}

@ImkeF How to get each combination into separate row ? 

 

IDvalue
11,11
22,22
33,33

you could use the tool Merge Columns, after having selected the involved columns:

image.png

 

then choice the separator you like:

 

image.png

 

 

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 @function.

 

 

 

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

 

 

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors