Open a list considering columns

[wribeiro]

Hello everyone, I hope you are doing well. I have a challenging situation and i would like your help.

I have three columns. StartYear, EndYear, List as you see in the following image.

I neeed open the list and create a relation between years (Start and End) and the list (considering the sequence of the numbers).

You are able to see an example below.

StartYear: 2010

EndYear: 2020
List: (5,4,3,6,7,8,9,2,9,0,5)

The final return will be two columns as you see below:

2010,5

2011,4

2012,3

2013,6

2014,7

2015,8

2016,9

2017,2

2018,9

2019,0

2020,5

Could you help me with this challenging script?

Thank you

[AlienSx · Accepted solution]

Hi, @wribeiro try this

let
    Source = <your_source_table>,
    type_int = Table.TransformColumnTypes(Source,{{"STARTYEAR", Int64.Type}, {"ENDYEAR", Int64.Type}}),
    z = Table.Combine(Table.AddColumn(type_int, "Custom", each Table.FromColumns({{[STARTYEAR]..[ENDYEAR]} , [JSON_ARRAY]}))[Custom])
in
    z

[wribeiro]

Just one more question. I wanna keep my old columns. Is it possible?

[AlienSx]

it is 

let
    Source = your_source_table,
    type_int = Table.TransformColumnTypes(Source,{{"STARTYEAR", Int64.Type}, {"ENDYEAR", Int64.Type}}),
    z = Table.ExpandTableColumn(Table.AddColumn(type_int, "Custom", each Table.FromColumns({{[STARTYEAR]..[ENDYEAR]} , [JSON_ARRAY]})),"Custom", {"Column1", "Column2"})
in
    z

[wribeiro]

Amazing. Thank you

[wribeiro]

Incredible. Thank you

[tackytechtom]

Hii @wribeiro ,

My workflow would look something like this:

1) Add / unfold all the years between StartDate and EndDate as new rows (see below screenshot)

2) Add an index for each row 

3) Open the list 

4) Add an index for each row of the list

5) Join the first index with the second index

6) Add column as concatenation of the previous join/merge statement.

To unfold all the rows between two dates you can use this one here:

https://www.tackytech.blog/how-to-swiftly-take-over-power-query/#1_Unfold_expand_create_rows_between...

I'd love to help ypu more, but it is hard to add that list example manually (rather, I was too lazy to create a nested JSON to imitate your case). Feel free to share the Power BI or JSON file in case you need more support 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[wribeiro]

Thank you so much. I think... almost there. Could you give me a final explanation, please? I have attached my PowerBI sample file. In fact, I was expecting just 17 rows in this example.

https://drive.google.com/file/d/11gxDsEJrtCKAXcjh-AZzTthJ0LkOEUHl/view?usp=sharing