Generate a list from data stored in two columns

[jeffreyweir]

Hi folks. I'm trying to 'expand'  a Table by creating a new column that for each record contains all the numbers between TP_Start and TP_End.  Here's what I've got currently:

I know that if I use this:

List.Generate(()=>0, each _ < 10, each _ + 1)

...and then expand to new rows, I'll get this:

...but if I replace those constants with my column like so:

= Table.AddColumn(#"Expanded TimePeriod", "Custom", each List.Generate(()=>[TP_Start], each _ <= [TP_End], each _ + 1))

...I get an error "Expression.Error: We cannot apply field access to the type Number."

I've seen a few cases on the net including here that address this, but I don't understand the solution, and wonder if I'm even using the simplest method to generate a list from two numbers stored in two columns.

Here's my code so far:

let
    Source = Table.NestedJoin(Offers_Electricity,{"Time"},TimePeriod,{"TimePeriod"},"TimePeriod",JoinKind.LeftOuter),
    #"Expanded TimePeriod" = Table.ExpandTableColumn(Source, "TimePeriod", {"TP_Start", "TP_End"}, {"TP_Start", "TP_End"})
in
    #"Expanded TimePeriod"

[Ashish_Mathur]

Hi,

Try this M code instead

={Number.From([TP_Start])..Number.From([TP_End])}

[francisco_marco]

It works perfectly. If the columns are already a number is even simpler: {[TP_Start]..[TP_End]}

[Ashish_Mathur]

Glad to know that.

[jeffreyweir · Accepted solution]

Ahh...I see now that I was trying to shoot a mosquito with a cannon, to quote MarcelBeug

#"Added Custom" = Table.AddColumn(#"Expanded TimePeriod", "TradingPeriod", each {[TP_Start]..[TP_End]}),