Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi @RichaBansal
With that exact data no, but if the Start and End Columns were formated as dates, then yes.
Date.Year([End])*12+Date.Month([End])-Date.Year([Start])*12-Date.Month([Start])
[Amount]/ [Custom]
List.Numbers(1,[Custom])
Date.StartOfMonth(
Date.AddMonths(
[Start],
[Intervals] -1
)
))
This should help
Joe
If you found my answer helpful and it solved your issue, please accept as solution
I think I missed a step. You will need to expand the list to new rows before adding the last step
Hi @RichaBansal
With that exact data no, but if the Start and End Columns were formated as dates, then yes.
Date.Year([End])*12+Date.Month([End])-Date.Year([Start])*12-Date.Month([Start])
[Amount]/ [Custom]
List.Numbers(1,[Custom])
Date.StartOfMonth(
Date.AddMonths(
[Start],
[Intervals] -1
)
))
This should help
Joe
If you found my answer helpful and it solved your issue, please accept as solution
Hi Joe,
The start and end are dates. I got the list of numbers, but the Last column shows error.
I think I missed a step. You will need to expand the list to new rows before adding the last step
Worked like a charm 🙂
Thank you Joe. Have a pleasant day.
This will work only in case when Start and End are defined as dates. @RichaBansal, let us know if input Start and End are dates or just text "Jan" and "April"
@RichaBansal, in case you have input stored as text, try this:
Result:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JRciwoyswB0oYGIKAUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t, Amount = _t]),
Transformed = Table.TransformColumns(Source, {
{"Start", each Date.Month(Date.FromText("2024" & Text.Start(_,3) & "01", [Format="yyyyMMMdd", Culture="en-US"])), Int64.Type},
{"End", each Date.Month(Date.FromText("2024" & Text.Start(_,3) & "01", [Format="yyyyMMMdd", Culture="en-US"])), Int64.Type},
{"Amount", Number.From, type number}
}),
Ad_FinalTable = Table.AddColumn(Transformed, "FinalTable", each
[ a = [Amount] / ([End]-[Start]+1),
b = List.Transform({[Start]..[End]}, (x)=> Date.ToText(#date(2024,x,1), "MMM", "en-US")),
c = Table.FromColumns({ b, List.Repeat({a}, List.Count(b)) }, type table[Month=text, Amount=number])
][c], type table),
FinalTable = Table.Combine(Ad_FinalTable[FinalTable])
in
FinalTable
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.