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
AlienSx's avatar
AlienSx
Super User
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
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

  • AlienSx's avatar
    AlienSx
    Super User
    3 years ago

    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
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Amazing. Thank you