Parse Numbers from Text to create Number List and add text to each value

[aklaur]

  Trying to  create a list from Range with added Text before (or after?)   Combining Text from list.

 Goal:

Area	Expanded
Lots 1 ‐ 10	Lot 1, Lot 2, Lot 3, Lot 4, Lot 5, Lot 6, Lot 7, Lot 8, Lot 9, Lot 10
Lots 5-6 &9	Lot 5, Lot 6, Lot 9
Lots 3-5 & 7-9	Lot 3, Lot 4, Lot 5, Lot 7, Lot 8, Lot 9

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Area", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Area]),each if Value.Is(Value.FromText(_), type number) then _ else null)))),
    #"Inserted First Characters" = Table.AddColumn(#"Added Custom", "First Characters", each Text.Start([Custom], 1), type text),
    #"Split Column by Positions" = Table.SplitColumn(#"Inserted First Characters", "Custom", Splitter.SplitTextByPositions({1}), {"Custom.1"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Positions",{{"Custom.1", Int64.Type}, {"First Characters", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Expanded", each Text.Combine(List.Transform(List.Transform(List.Numbers([First Characters],[Custom.1]),each Number.ToText(_)),each Text.Combine("Lot",(_))),","))
in
    #"Added Custom1"

 I know the previous Query may not work for multiple Ranges, perhaps a better way to Parse.

Mostly stuck on the last line though:

#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Expanded", each Text.Combine(List.Transform(List.Transform(List.Numbers([First Characters],[Custom.1]),each Number.ToText(_)),each Text.Combine("Lot",(_))),","))

[MarkLaf · Accepted solution]

I think this gets what you want.

let
    Source = OriginalAreas,
    ExpandLots = Table.AddColumn(
        Source,
        "Expanded",
        each
            [
                removeLots = Text.Trim(Text.Replace([Area], "Lots", "")),
                ampSplit = Text.Split(removeLots, "&"),
                dashSplit = List.Transform(
                    ampSplit,
                    each [
                        split = Text.SplitAny(_, "-‐"), 
                        toNum = List.Transform(split, Number.FromText)
                    ][toNum]
                ),
                expandDash = List.Transform(dashSplit, each if List.Count(_) = 1 then _ else {_{0}.._{1}}),
                combo = List.Combine(expandDash),
                concatLot = "Lot " & Text.Combine(List.Transform(combo, Text.From), ", Lot ")
            ][concatLot],
        type text
    )
in
    ExpandLots

[aklaur]

Awesome That Works! Thanks So much!   Much more complicated than my  average expertise.