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

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors