cancel
Showing results for
Did you mean:

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.

New Member

## the number of times unpivoting a row

Hi PBI Experts,

Can we achieve this?

2 ACCEPTED SOLUTIONS
Responsive Resident

With that exact data no, but if the Start and End Columns were formated as dates, then yes.

• Add a custom column  to create month amount between dates

``Date.Year([End])*12+Date.Month([End])-Date.Year([Start])*12-Date.Month([Start])​``

• Create a column to calculate Monthly Amount

``[Amount]/ [Custom]​``

• Create another column called "Intervals"

``List.Numbers(1,[Custom])​``

• Then add another to create the months

``````Date.StartOfMonth(
[Start],
[Intervals] -1
)
))​``````

This should help

Joe

Responsive Resident

I think I missed a step. You will need to expand the list to new rows before adding the last step

6 REPLIES 6
Responsive Resident

With that exact data no, but if the Start and End Columns were formated as dates, then yes.

• Add a custom column  to create month amount between dates

``Date.Year([End])*12+Date.Month([End])-Date.Year([Start])*12-Date.Month([Start])​``

• Create a column to calculate Monthly Amount

``[Amount]/ [Custom]​``

• Create another column called "Intervals"

``List.Numbers(1,[Custom])​``

• Then add another to create the months

``````Date.StartOfMonth(
[Start],
[Intervals] -1
)
))​``````

This should help

Joe

New Member

Hi Joe,

The start and end are dates. I got the list of numbers, but the Last column shows error.

Responsive Resident

I think I missed a step. You will need to expand the list to new rows before adding the last step

New Member

Worked like a charm 🙂

Thank you Joe. Have a pleasant day.

Super User

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"

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Super User

@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}
}),
[ 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),
in
FinalTable``````

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors