Forum Discussion

ogend's avatar
ogend
Helper II
4 years ago
Solved

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)
mango
pineapple

watermelon

 

list (2)
pineapple
watermelon

 

list(3)
guava
blueberry

 

list(4)
orange
lemon
kiwi
peach

 

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    4 years ago

    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"

     

2 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    4 years ago

    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"