Forum Discussion
Splitting period data into months
Hello,
I have my data in the following structure:
ID BEGIN END VALUE
1 01.10.2017 31.12.2017 90
2 01.01.2016 31.10.2017 1000
....
I need to present this data on a time axis, so that I see the values overlapping and summing up for each month.
Now, I would like to either split the data into more rows, so that the period (Begin-End) will be splitted into single months. E.g. for first row (see above), I would have months: October 2017, November 2017, December 2017. And for each of these rows the original value would be divided by the number of the rows, in this case it would be 90/3=30.
Another approach would be to use the current data structure, but I don't know how to present such time periods (from-until) on a chart. Is it possible at all?
Thank you in advance for your suggestions!
Another approach
Hi lkarolak,
In your scenario, you can create a function use a blank query like below:
(startdate as date, enddate as date) as table => let //startdate=#date(2015,8,1), //enddate=#date(2016,1,31), Source = {Number.From(startdate)..Number.From(enddate)}, #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}), #"Calculated End of Month" = Table.TransformColumns(#"Renamed Columns",{{"Date", Date.EndOfMonth, type date}}), #"Removed Duplicates" = Table.Distinct(#"Calculated End of Month"), #"Inserted End of Year" = Table.AddColumn(#"Removed Duplicates", "EndOfYear", each Date.EndOfYear([Date]), type date), #"Grouped Rows" = Table.Group(#"Inserted End of Year", {}, {{"Original", each _, type table}, {"Months_Total", each Table.RowCount(_), type number}}) in #"Grouped Rows"Then rename the function as fnGetAllocationBase.
In the query which get the fact table, invoke this function like below:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDTXNTTQRbCNdI1BHEsDpVidaCUjiLiZrqEusnKwEkMDA6CiWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, BEGIN = _t, END = _t, VALUE = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"BEGIN", type date}, {"END", type date}, {"VALUE", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each fnGetAllocationBase([BEGIN],[END])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"BEGIN", "END"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Original", "Months_Total"}, {"Custom.Original", "Custom.Months_Total"}), #"Expanded Custom.Original" = Table.ExpandTableColumn(#"Expanded Custom", "Custom.Original", {"Date", "EndOfYear"}, {"Custom.Original.Date", "Custom.Original.EndOfYear"}), #"Added Custom1" = Table.AddColumn(#"Expanded Custom.Original", "Value_update", each [VALUE]/[Custom.Months_Total]), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.Original.Date", type date}, {"Custom.Original.EndOfYear", type date}}) in #"Changed Type1"Reference:
Allocate Units Based on Dates Using Power Query
Best Regards,
Qiuyun Yu
5 Replies
v-qiuyu-msftCommunity Support
Hi lkarolak,
In your scenario, you can create a function use a blank query like below:
(startdate as date, enddate as date) as table => let //startdate=#date(2015,8,1), //enddate=#date(2016,1,31), Source = {Number.From(startdate)..Number.From(enddate)}, #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}), #"Calculated End of Month" = Table.TransformColumns(#"Renamed Columns",{{"Date", Date.EndOfMonth, type date}}), #"Removed Duplicates" = Table.Distinct(#"Calculated End of Month"), #"Inserted End of Year" = Table.AddColumn(#"Removed Duplicates", "EndOfYear", each Date.EndOfYear([Date]), type date), #"Grouped Rows" = Table.Group(#"Inserted End of Year", {}, {{"Original", each _, type table}, {"Months_Total", each Table.RowCount(_), type number}}) in #"Grouped Rows"Then rename the function as fnGetAllocationBase.
In the query which get the fact table, invoke this function like below:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDTXNTTQRbCNdI1BHEsDpVidaCUjiLiZrqEusnKwEkMDA6CiWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, BEGIN = _t, END = _t, VALUE = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"BEGIN", type date}, {"END", type date}, {"VALUE", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each fnGetAllocationBase([BEGIN],[END])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"BEGIN", "END"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Original", "Months_Total"}, {"Custom.Original", "Custom.Months_Total"}), #"Expanded Custom.Original" = Table.ExpandTableColumn(#"Expanded Custom", "Custom.Original", {"Date", "EndOfYear"}, {"Custom.Original.Date", "Custom.Original.EndOfYear"}), #"Added Custom1" = Table.AddColumn(#"Expanded Custom.Original", "Value_update", each [VALUE]/[Custom.Months_Total]), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.Original.Date", type date}, {"Custom.Original.EndOfYear", type date}}) in #"Changed Type1"Reference:
Allocate Units Based on Dates Using Power Query
Best Regards,
Qiuyun YuThanks Qiuyun Yu, that seems to do the trick!
Wow, this is an AWESOME solution!
It helped me very much, thank you Qiuyun!
Please tell me, where can I learn more of those tricks and advanced scenarios working with Power Query.
I mean manipulating tables / using functions, etc.
Would you recommend some site / book / youtube?
Thanks
Michael
