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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bhm
Frequent Visitor

Convert first month to M1 and then increase by 1 for subsequent months

Hi,

I am trying to develop a visual with multiple projects monthly information. Every project has a different start and end date. I am trying to get the timeline into M1, M2, M3 and so on, irrespective of their calendar start or finish date. Any suggestion, what is the best way to achieve it?

 

For example:

Project 1Project 1Project 2Project 2Project 3Project 3
Jan-16M1Sep-15M1Apr-21M1
Feb-16M2Oct-15M2May-21M2
Mar-16M3Nov-15M3Jun-21M3
... and so on... and so on... and so on... and so on... and so on... and so on

 

Thanks in advance

BHM

1 ACCEPTED SOLUTION

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdGxCsMgFIXhVymSMYL3Rhsz1kCHLO0eMrQhSwktlPT9oxWF2rOd4UN/dBzF9f16LPN2OIlatHK4PSWxn3lUpJQSU/0rSUPKgDJBqhG1kDaAKi3Pyz2KPPCpRJBaRC2kx4K6GDB8fKIJrWn8B7gYACiX7+piAKAtkGzg/Ygq9nRNdE3UINpBWv5AH1KNvMybpM7vPCouTw2UGdHmS6cd", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project #" = _t, Weekending = _t, Month = _t, #"Amount Spent" = _t]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Weekending"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Amount Spent", Currency.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Project #", "Month"}, {{"Amount", each List.Sum([Amount Spent]), type nullable text}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Project #"}, {{"All", each Table.AddIndexColumn(_,"Index",1)}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows1", "All", {"Month", "Amount", "Index"}, {"Month", "Amount", "Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded All",{{"Month", "Month-Year"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Month", each "M-"&Text.From([Index])),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Project #", "Month-Year", "Month", "Amount"})
in
    #"Reordered Columns"

 

View solution in original post

4 REPLIES 4
Vijay_A_Verma
Super User
Super User

I believe what you are showing is output expected. We need to see your sample input data as well.

OK,

Input data something like

Project #WeekendingMonthAmount Spent
Project A7-Jan-12Jan-12$1000
Project A14-Jan-12Jan-12$1200
Project A21-Jan-12Jan-12$1400
Project A28-Jan-12Jan-12$1300
Project A04-Feb-12Feb-12$1400
Project A11-Feb-12Feb-12$1800
Project A18-Feb-12Feb-12$1600
... and so on   
Project B04-Jun-15Jun-15$1400
Project B11-Jun-15Jun-15$2000
Project B18-Jun-15Jun-15$700
Project B25-Jun-15Jun-15$1700
Project B02-Jul-15Jul-15$1500
Project B09-Jul-15Jul-15$1300
...and so on   
Project C15-Oct-19Oct-19$2500
Project C22-Oct-19Oct-19$3500
... and so on   

 

Now what I am trying to achieve is 

Project #Month-YearMonth NoAmount
Project-AJan-12M-1Sum of monthly spent in Jan-12
Project-AFeb-12M-2Sum of monthly spent in Feb-12
... and so on   
Project-BJun-15M-1Sum of monthly spent in Jun-15
Project-BJul-15M-2Sum of monthly spent in Jul-15
... and so on   
Project-COct-19M-1Sum of monthly spent in Oct-19
Project-CNov-19M-2Sum of monthly spent in Nov-19
... and so on   

 

I am trying to find monthwise spending pattern, from the start of the project till end of the project. So, need to convert start month as M1 and then increase by 1 for subsequent months. Then plot the curve by project by month of spending pattern. e.g. Month 1, Project A spent X amount, Project B spent Y amount, Project C spent Z amount and so on.

 

Hope this explains the scenario. ANy help is greatly appreciated.

 

BHM

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdGxCsMgFIXhVymSMYL3Rhsz1kCHLO0eMrQhSwktlPT9oxWF2rOd4UN/dBzF9f16LPN2OIlatHK4PSWxn3lUpJQSU/0rSUPKgDJBqhG1kDaAKi3Pyz2KPPCpRJBaRC2kx4K6GDB8fKIJrWn8B7gYACiX7+piAKAtkGzg/Ygq9nRNdE3UINpBWv5AH1KNvMybpM7vPCouTw2UGdHmS6cd", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project #" = _t, Weekending = _t, Month = _t, #"Amount Spent" = _t]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Weekending"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Amount Spent", Currency.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Project #", "Month"}, {{"Amount", each List.Sum([Amount Spent]), type nullable text}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Project #"}, {{"All", each Table.AddIndexColumn(_,"Index",1)}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows1", "All", {"Month", "Amount", "Index"}, {"Month", "Amount", "Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded All",{{"Month", "Month-Year"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Month", each "M-"&Text.From([Index])),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Project #", "Month-Year", "Month", "Amount"})
in
    #"Reordered Columns"

 

Awesome, thank you very much, it worked.

 

Thanks

BHM

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors