Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 ne...
Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    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
tackytechtom's avatar
tackytechtom
Most Valuable Professional
3 years ago

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:

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

 

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/