CODEWITHSUNDEEP
excelpowerbidax
dookieface
dookieface

Reputation: 21

Using DAX in Power BI, how do I expand ranged numbers within a cell?

I have a column that is ranged in both letters and numbers that I am trying to expand out into new rows. I also want the entire row within that range number to be duplicated into the new rows. Can it be done in excel?

Original table:

Column A Column B
A123-A125 Apples
B23-B24 Bananas

New table:

Column A Column B
A123 Apples
A124 Apples
A125 Apples
B23 Bananas
B24 Bananas

Upvotes: 0

Views: 53

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60389

Not sure about DAX, but you can do this using Power Query (Transform Data):
(Paste the code below into the Advanced Editor.
Replace the Source line with your actual data source)

let
    Source = Excel.CurrentWorkbook(){[Name="Table15"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}, {"Column B", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 
        let
          #"Split Hyphen" =  Text.Split([Column A],"-"), 
          #"Split Digits" = List.Transform(#"Split Hyphen", each 
            Splitter.SplitTextByCharacterTransition((c)=>not List.Contains({"0".."9"},c),{"0".."9"})(_)),
          #"List Numbers" = List.Numbers(Number.From(#"Split Digits"{0}{1}), 
                              try Number.From(#"Split Digits"{1}{1}) - Number.From(#"Split Digits"{0}{1}) +1 otherwise 1
                              )
        in 
           List.Transform(#"List Numbers", each #"Split Digits"{0}{0} & Text.From(_)), type {text}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column A"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom",{"Custom", "Column B"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "Column A"}})
in
    #"Renamed Columns"

Note that the above will also work if Column A does not contain a range.

enter image description here

Upvotes: 0

Michal
Michal

Reputation: 6107

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    
    AddCustomColumn = Table.AddColumn(Source, "ExpandedRange", each let
        RangeStart = Text.Middle([Column A], 1, Text.PositionOf([Column A], "-") - 1),
        RangeEnd = Text.Middle([Column A], Text.PositionOf([Column A], "-") + 1),
        StartNumber = Number.From(Text.Remove(RangeStart, {"A".."Z"})),
        EndNumber = Number.From(Text.Remove(RangeEnd, {"A".."Z"})),
        Prefix = Text.Remove(RangeStart, {"0".."9"}),
        RangeList = List.Transform({StartNumber..EndNumber}, each Prefix & Text.From(_))
    in
        RangeList),
    
    ExpandColumn = Table.ExpandListColumn(AddCustomColumn, "ExpandedRange"),

    RemoveOriginalColumn = Table.RemoveColumns(ExpandColumn, {"Column A"}),

    RenameColumns = Table.RenameColumns(RemoveOriginalColumn, {{"ExpandedRange", "Column A"}})

in
    RenameColumns

enter image description here

Upvotes: 0

Related Questions