Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have an Excel file showing the performance of several campaigns:
Start date | End date | Campaign | Cost |
Jan 1 2021 | Jan 4 2021 | TV | 1,000 |
Feb 1 2021 | Feb 5 2021 | Radio | 20,000 |
Jan 1 2021 |
| Paid search | 500 |
Jan 2 2021 |
| Paid search | 500 |
Jan 3 2021 |
| Paid search | 500 |
For analysis purposes, I’d like to split all this data by date. That means that if no end date is given, nothing needs to happen. However, if an end date is given, then for each date between the start date and the end date I’d like to have a row created that’s evenly spreading the costs. Once that’s done, the end date column is no longer neccessary Output example::
Start date |
| Campaign | Cost |
Jan 1 2021 |
| TV | 250 |
Jan 2 2021 |
| TV | 250 |
Jan 3 2021 |
| TV | 250 |
Jan 4 2021 |
| TV | 250 |
Jan 1 2021 |
| Radio | 4,000 |
Jan 2 2021 |
| Radio | 4,000 |
Jan 3 2021 |
| Radio | 4,000 |
Jan 4 2021 |
| Radio | 4,000 |
Jan 5 2021 |
| Radio | 4,000 |
Jan 1 2021 |
| Paid search | 500 |
Jan 2 2021 |
| Paid search | 500 |
Jan 3 2021 |
| Paid search | 500 |
I found this post https://community.powerbi.com/t5/Desktop/Create-rows-for-every-date-in-date-range/m-p/731818#M353041 that already explains how I can create a row for each date. But how do I exclude rows where no end date is given? And how do I make sure that the cost in this case is showing the average for each row instead of repeating the number?
Best regards
Bas
Solved! Go to Solution.
This is one way you can do this...
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJQ0gEyjeHsxIKCnNRiIMPUwEApVgdFHRAlJeYBIUjeCCQfCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t, EndDate = _t, Name = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}, {"Name", type text}, {"Value", Int64.Type}}),
date_diff = Table.AddColumn(#"Changed Type", "date_diff", each Duration.Days([EndDate] - [StartDate]), Int64.Type),
remove_neg_dates = Table.SelectRows ( date_diff, each [date_diff] >= 0 or [date_diff] = null ),
list_dates = Table.AddColumn ( remove_neg_dates, "NewColumn", each if [date_diff] = null then null else List.Dates(Date.From([StartDate]),[date_diff]+1,#duration(1,0,0,0))),
expand = Table.ExpandListColumn(list_dates, "NewColumn"),
change_type = Table.TransformColumnTypes(expand,{{"NewColumn", type date}}),
remove_dates_diff = Table.RemoveColumns ( change_type, {"date_diff"} )
in
remove_dates_diff
Before
After
This is one way you can do this...
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJQ0gEyjeHsxIKCnNRiIMPUwEApVgdFHRAlJeYBIUjeCCQfCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t, EndDate = _t, Name = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}, {"Name", type text}, {"Value", Int64.Type}}),
date_diff = Table.AddColumn(#"Changed Type", "date_diff", each Duration.Days([EndDate] - [StartDate]), Int64.Type),
remove_neg_dates = Table.SelectRows ( date_diff, each [date_diff] >= 0 or [date_diff] = null ),
list_dates = Table.AddColumn ( remove_neg_dates, "NewColumn", each if [date_diff] = null then null else List.Dates(Date.From([StartDate]),[date_diff]+1,#duration(1,0,0,0))),
expand = Table.ExpandListColumn(list_dates, "NewColumn"),
change_type = Table.TransformColumnTypes(expand,{{"NewColumn", type date}}),
remove_dates_diff = Table.RemoveColumns ( change_type, {"date_diff"} )
in
remove_dates_diff
Before
After
You're very welcome!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.