Forum Discussion
Open a list considering columns
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
Hi, Anonymous 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
7 Replies
Hii Anonymous ,
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:
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/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
Hi, Anonymous 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 zit 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
