Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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"
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 # | 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
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