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

[bhm]

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 1	Project 1	Project 2	Project 2	Project 3	Project 3
Jan-16	M1	Sep-15	M1	Apr-21	M1
Feb-16	M2	Oct-15	M2	May-21	M2
Mar-16	M3	Nov-15	M3	Jun-21	M3
... and so on	... and so on	... and so on	... and so on	... and so on	... and so on

Thanks in advance

BHM

[Vijay_A_Verma]

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

[bhm]

OK,

Input data something like

Project #	Weekending	Month	Amount Spent
Project A	7-Jan-12	Jan-12	$1000
Project A	14-Jan-12	Jan-12	$1200
Project A	21-Jan-12	Jan-12	$1400
Project A	28-Jan-12	Jan-12	$1300
Project A	04-Feb-12	Feb-12	$1400
Project A	11-Feb-12	Feb-12	$1800
Project A	18-Feb-12	Feb-12	$1600
... and so on
Project B	04-Jun-15	Jun-15	$1400
Project B	11-Jun-15	Jun-15	$2000
Project B	18-Jun-15	Jun-15	$700
Project B	25-Jun-15	Jun-15	$1700
Project B	02-Jul-15	Jul-15	$1500
Project B	09-Jul-15	Jul-15	$1300
...and so on
Project C	15-Oct-19	Oct-19	$2500
Project C	22-Oct-19	Oct-19	$3500
... and so on

Now what I am trying to achieve is 

Project #	Month-Year	Month No	Amount
Project-A	Jan-12	M-1	Sum of monthly spent in Jan-12
Project-A	Feb-12	M-2	Sum of monthly spent in Feb-12
... and so on
Project-B	Jun-15	M-1	Sum of monthly spent in Jun-15
Project-B	Jul-15	M-2	Sum of monthly spent in Jul-15
... and so on
Project-C	Oct-19	M-1	Sum of monthly spent in Oct-19
Project-C	Nov-19	M-2	Sum 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

[Vijay_A_Verma · 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"

[bhm]

Awesome, thank you very much, it worked.

Thanks

BHM