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

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

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!.

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