Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
jeffreyweir
Helper III
Helper III

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

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

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

 

Try this M code instead

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Glad to know that.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.