cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

power query- generate a list for each record using "offset down" logic

Hi Power Query Gurus, 

 

Is it possible to generate a list of records with the following pattern?

I have 2 columns: "Text" and "Number".  "Number" provides a count of records I need in the list, and the records would be taken from the "Text" column itslef by offsetting down from the record 

textnumberList
apple0 
pear 0 
plum3list (1)
mango2list (2)
pineapple0 
watermelon0 
strawberry2list(3)
guava0 
blueberry4list(4)
orange0 
lemon0 
kiwi0 
peach0 
apricot0 

 

Expected values in  each list:

list (1) list (2) list(3) list(4)
mango pineappleguava orange
pineapplewatermelonblueberrylemon
watermelon    kiwi
      peach
1 ACCEPTED SOLUTION

Accepted Solutions
Syndicate_Admin
Administrator
Administrator

First add an Index column (with a starting index of 0).

Then add a custom column with the formula:

 

= if [number]=0 then null 
    else List.Range(#"Added Index"[text],[Index]+1,[number])

 

 

Finally, remove that Index column

The M Code to reproduce this, which you can paste into a blank query:

 

 

let

//the data
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY5LCoAwDESvIlm7EPU2xUWUoMX0Q2wt3t6iWHX5ZjKTUQrQeyaooYGhVuAJpXqJo8nQXWDQzi5Te1va0j+aMJAYYmeLtAXBNJLIUXJzxB3LwciRHr+/FCf5zVvKZD59q076O3VaCqEXPblw83AC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [text = _t, number = _t]),

//set the data types
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"text", type text}, {"number", Int64.Type}}),

    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),

//add custom column
    #"Added Custom" = Table.AddColumn(#"Added Index", "List", 
        each if [number]=0 then null 
                else List.Range(#"Added Index"[text],[Index]+1,[number])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

 

View solution in original post

2 REPLIES 2
Syndicate_Admin
Administrator
Administrator

First add an Index column (with a starting index of 0).

Then add a custom column with the formula:

 

= if [number]=0 then null 
    else List.Range(#"Added Index"[text],[Index]+1,[number])

 

 

Finally, remove that Index column

The M Code to reproduce this, which you can paste into a blank query:

 

 

let

//the data
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY5LCoAwDESvIlm7EPU2xUWUoMX0Q2wt3t6iWHX5ZjKTUQrQeyaooYGhVuAJpXqJo8nQXWDQzi5Te1va0j+aMJAYYmeLtAXBNJLIUXJzxB3LwciRHr+/FCf5zVvKZD59q076O3VaCqEXPblw83AC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [text = _t, number = _t]),

//set the data types
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"text", type text}, {"number", Int64.Type}}),

    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),

//add custom column
    #"Added Custom" = Table.AddColumn(#"Added Index", "List", 
        each if [number]=0 then null 
                else List.Range(#"Added Index"[text],[Index]+1,[number])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

 

Syndicate_Admin
Administrator
Administrator

@ronrsnfld  This is great, thank you so much!

Helpful resources

Announcements
Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Top Kudoed Authors
Users online (5,375)