cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jeffreyweir Member
Member

Generate a list from data stored in two columns

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:

 

14-09-2018 2-13-48 PM.jpg

 

I know that if I use this:

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

 

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

 

14-09-2018 2-36-02 PM.jpg

 

...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"
1 ACCEPTED SOLUTION

Accepted Solutions
jeffreyweir Member
Member

Re: Generate a list from data stored in two columns

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]}),

2 REPLIES 2
Super User
Super User

Re: Generate a list from data stored in two columns

Hi,

 

Try this M code instead

 

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

jeffreyweir Member
Member

Re: Generate a list from data stored in two columns

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]}),