Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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"
Solved! Go to 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]}),
Hi,
Try this M code instead
={Number.From([TP_Start])..Number.From([TP_End])}
It works perfectly. If the columns are already a number is even simpler: {[TP_Start]..[TP_End]}
Glad to know that.
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]}),
User | Count |
---|---|
73 | |
70 | |
38 | |
24 | |
23 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |