Forum Discussion
How to repeat values until superseded?
Hi,
I would like to upload an Excel file with budget data for a given month (e.g., Jan 2020 - $4,000) and have the data be repeated for all subsequent months until it is superseded (e.g., May 2020 - $4,500). What is th ebest way to do this, other than repeating it in Excel?
I think this is a simple problem but I can't find an answer after quite a bit of searching.
Many thanks
Thanks for the demo data. Here is the M code to see one way to accomplish this one. This one is a good example of how powerful M is. Filling down whole tables!
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tVA9D4IwEP0rTWcSKArqqolxMRqDE2Go0EiTekfaEuXf2w5EPlZc7vveu3t5TlnIwjhiOxrQ1dqZM4eKW9Sdi0/YGtF7spdKGZcdsIVSKpLxj59HsLXy09uIFsEYkDFnjxK4W+BqEu9Ra3xLeJKQ3ETDu5cA6wnuYETZalERhRyI7nsjNpbM6dJ48kCmOZgGtV+92FpoMqxc24eS5aj0w98M4eNoAXmSGeA/5Unn9y8qj/um+AI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Category_ID = _t, Category_L0 = _t, Category_L1 = _t, Category_L2 = _t, Category_L3 = _t, Recurrence_Frequency = _t, Amount = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Category_ID", Int64.Type}, {"Category_L0", type text}, {"Category_L1", type text}, {"Category_L2", type text}, {"Category_L3", type text}, {"Recurrence_Frequency", type text}, {"Amount", Int64.Type}}),
GroupedRows = Table.Group(#"Changed Type", {"Date"}, {{"AllRows", each _, type table [Date=date, Category_ID=number, Category_L0=text, Category_L1=text, Category_L2=text, Category_L3=text, Recurrence_Frequency=text, Amount=number]}}),
Custom1 = List.Transform({0..23}, each Date.AddMonths(#date(2019,1,1), _)),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type1" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column1", "Date"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Date"}, GroupedRows, {"Date"}, "Changed Type1", JoinKind.LeftOuter),
#"Expanded Changed Type1" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type1", {"AllRows"}, {"AllRows"}),
#"Filled Down" = Table.FillDown(#"Expanded Changed Type1",{"AllRows"}),
#"Expanded AllRows" = Table.ExpandTableColumn(#"Filled Down", "AllRows", {"Category_ID", "Category_L0", "Category_L1", "Category_L2", "Category_L3", "Recurrence_Frequency", "Amount"}, {"Category_ID", "Category_L0", "Category_L1", "Category_L2", "Category_L3", "Recurrence_Frequency", "Amount"})
in
#"Expanded AllRows"If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
8 Replies
You can do this in the query editor by starting with a list of all possible dates/months, convert to table, and then merging/expanding in the data/query you do have, and then doing a Fill Down. In your case, Jan-Apr would have the same values and then be "replaced" in May with the new number.
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
srholmes , Not very clear
you can create a calendar and put logic based on month and do it
Target = if(Eomonth('Date'[Date],0)<=Eomonth(TODAY(),-1) && Data[Date]= Eomonth('Date'[Date],0),4000,4500)
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.Thanks for the answers everyone.
Here is a sample of what I am trying to achieve:
There are several categories and I would like to create a monthly budget for each category that is repeated each month based on the input values until I change them at some later review point. I will have many more categories so would prefer not to have to do them all manually.
Also I do have the date query set up too and will link to it.
Many thanks
SimonThanks for the demo data. Here is the M code to see one way to accomplish this one. This one is a good example of how powerful M is. Filling down whole tables!
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tVA9D4IwEP0rTWcSKArqqolxMRqDE2Go0EiTekfaEuXf2w5EPlZc7vveu3t5TlnIwjhiOxrQ1dqZM4eKW9Sdi0/YGtF7spdKGZcdsIVSKpLxj59HsLXy09uIFsEYkDFnjxK4W+BqEu9Ra3xLeJKQ3ETDu5cA6wnuYETZalERhRyI7nsjNpbM6dJ48kCmOZgGtV+92FpoMqxc24eS5aj0w98M4eNoAXmSGeA/5Unn9y8qj/um+AI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Category_ID = _t, Category_L0 = _t, Category_L1 = _t, Category_L2 = _t, Category_L3 = _t, Recurrence_Frequency = _t, Amount = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Category_ID", Int64.Type}, {"Category_L0", type text}, {"Category_L1", type text}, {"Category_L2", type text}, {"Category_L3", type text}, {"Recurrence_Frequency", type text}, {"Amount", Int64.Type}}),
GroupedRows = Table.Group(#"Changed Type", {"Date"}, {{"AllRows", each _, type table [Date=date, Category_ID=number, Category_L0=text, Category_L1=text, Category_L2=text, Category_L3=text, Recurrence_Frequency=text, Amount=number]}}),
Custom1 = List.Transform({0..23}, each Date.AddMonths(#date(2019,1,1), _)),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type1" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column1", "Date"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Date"}, GroupedRows, {"Date"}, "Changed Type1", JoinKind.LeftOuter),
#"Expanded Changed Type1" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type1", {"AllRows"}, {"AllRows"}),
#"Filled Down" = Table.FillDown(#"Expanded Changed Type1",{"AllRows"}),
#"Expanded AllRows" = Table.ExpandTableColumn(#"Filled Down", "AllRows", {"Category_ID", "Category_L0", "Category_L1", "Category_L2", "Category_L3", "Recurrence_Frequency", "Amount"}, {"Category_ID", "Category_L0", "Category_L1", "Category_L2", "Category_L3", "Recurrence_Frequency", "Amount"})
in
#"Expanded AllRows"If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
