Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
shaunm001
New Member

Expand a list of various types into a new list

Trying to figure out how to programatically expand a list of various data types, including other lists. 

 

For example, here is a list of lists:

 

list1.PNG

 

If I "drill down" to the next level using List.Combine, I now have more lists, and some text values:

 

list2.PNG

 

The first List contained two Lists, the second List contained two text values.

 

Now I want to drill down again into those two list items, but I can't use List.Combine anymore, because that only works with data type List, and now I have some text values in my list. I want keep the text values as part of the new list, but continue drilling down into nested lists, until eventually I have a list of all text values. 

 

Any help would be greatly appreaciated! 

1 ACCEPTED SOLUTION
shaunm001
New Member

Thanks guys, I ended up using this function, works great!

 

Capture.PNG

 

 

View solution in original post

4 REPLIES 4
shaunm001
New Member

Thanks guys, I ended up using this function, works great!

 

Capture.PNG

 

 

ziying35
Impactful Individual
Impactful Individual

@shaunm001 

You want is similar to {{"a", "b"}, {"c", "d"}, "e", "f", {"g", {"h", "i"}}} to convert to {"a", "b", "c", "d", "e", "f", "g", "h", "i"}?

Hi @shaunm001 

try this, a recursive function that keeps using List.Combine until all lists have been expanded. For illustration purposes, it uses the list created in Source = and you'll see the result in the last step

let
  
  expandFunc_ = (inputList_)=>
  let
    step0 = List.Combine(List.Transform(inputList_, each if Value.Type(_) = type list then _ else {_})),
    step1 = List.Accumulate(inputList_, true, (state, current)=> state and Value.Type(current) <> type list  ),
    output = if step1 = true then step0 else @expandFunc_(step0) 
  in 
   output,  
  
  Source = {{"a", "b"}, {"c", "d"}, "e", "f", {"g", {"h", {"i",{"j", "l", "m"}}}}},
  resfunc = expandFunc_(Source)
in
  resfunc

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

SU18_powerbi_badge

Greg_Deckler
Super User
Super User

Seems to me that you will need a new column where you make the decision to drill down or not based upon the data type. You could probably turn around and combine that into a single step in your M code.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.