locked
List with from and to year - Transformed to a table RRS feed

  • Question

  • I have a list that consist of three columns: Product, FromYear, ToYear.

    Is it possible in Power Query to tranform this information to at list with one row for each year?


    Norway - Oslo

    Wednesday, April 13, 2016 8:00 AM

Answers

  • The following steps can be performed through the UI (I renamed default step names to remove spaces). AddedCustom is the key step:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"Description", type text}, {"From", Int64.Type}, {"To", Int64.Type}}),
        AddedCustom = Table.AddColumn(ChangedType, "Custom", each {[From]..[To]}),
        ExpandedCustom = Table.ExpandListColumn(AddedCustom, "Custom"),
        RemovedColumns = Table.RemoveColumns(ExpandedCustom,{"From", "To"})
    in
        RemovedColumns

    Wednesday, April 13, 2016 12:52 PM

All replies

  • I'm afraid I don't understand what your input and desired output looks like. If you have

    Toothpaste|1987|2016
    Slippers|2013|2015

    What would your desired output be?

    • Edited by Dale Hohm Wednesday, April 13, 2016 10:39 AM
    Wednesday, April 13, 2016 10:36 AM
  • The output in your case is:

    Toothpaste, 1987

    Toothpaste, 1988

    Toothpaste, 1989

    ...

    Slippers, 2013

    Slippers, 2014

    Slippers, 2015

    Helge


    Norway - Oslo

    • Proposed as answer by Dale Hohm Wednesday, April 13, 2016 1:44 PM
    • Unproposed as answer by Dale Hohm Wednesday, April 13, 2016 1:44 PM
    Wednesday, April 13, 2016 11:42 AM
  • The following steps can be performed through the UI (I renamed default step names to remove spaces). AddedCustom is the key step:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"Description", type text}, {"From", Int64.Type}, {"To", Int64.Type}}),
        AddedCustom = Table.AddColumn(ChangedType, "Custom", each {[From]..[To]}),
        ExpandedCustom = Table.ExpandListColumn(AddedCustom, "Custom"),
        RemovedColumns = Table.RemoveColumns(ExpandedCustom,{"From", "To"})
    in
        RemovedColumns

    Wednesday, April 13, 2016 12:52 PM
  • Thank you so much. What a magic tool. This solution save me lots of time.

    Norway - Oslo

    Thursday, April 14, 2016 6:47 AM