none
Split column into rows and with consecutive value RRS feed

  • Question

  • Hello Everyone,

    I have an excel column, some of the cells have contents like 120-123, which I want to split the column by delimiter - into rows, and then add row values as 120, 121, 122, 123. Do not know how to do this in power query, can anyone please help on this?

    Rachel

    Monday, April 16, 2018 7:57 PM

Answers

  • Hi Rachel,

    The following is an example of how your requirement can be accomplished. In the example, I am using an Excel table with a column named "Value Range". Adjust your column name accordingly.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        NumberList = Table.TransformColumns(Source, {"Value Range", each {Number.From(Text.BeforeDelimiter(_, "-"))..Number.From(Text.AfterDelimiter(_, "-"))}}),
        ExpandedList = Table.ExpandListColumn(NumberList, "Value Range")
    in
        ExpandedList

    Monday, April 16, 2018 10:25 PM

All replies