Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hey guys! I hope you can help me with some issues.
I have a table of active advertising campaigns with three columns: start date, end date, and de campaign ID.
I also have a date table.
But these two tables have no relationship in the model.
What I would like is to count the number of active campaigns on a certain date.
StartDate | EndDate | CampaignID |
14/12/2022 | 30/12/2022 | TRC-1 |
22/12/2022 | 13/01/2023 | TRC-2 |
In this example, the number of active campaigns on 20/12/2022 is 2 and on 04/01/2023 is 1.
Any help would be very appreciated!
Solved! Go to Solution.
Hi @Juan_Quikin5 ,
You could try something like this in Power Query. Please open a blank query--> Advanced editor-->Remove any existing code and copy and paste the below code.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTRNzTSNzIwMlJQUNJRMjZA4YYEOesaKsXqRCsZGcElgOKGxvoGhiCeMVSRkVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t, EndDate = _t, CampaignID = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}, {"CampaignID", type text}}),
#"Added Date" = Table.AddColumn(#"Changed Type", "Date", each {Number.From([StartDate])..Number.From([EndDate])}),
#"Expanded Date" = Table.ExpandListColumn(#"Added Date", "Date"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"Date"}, {{"CampaignCount", each Table.RowCount(_), Int64.Type}})
in
#"Grouped Rows"
Input
Output
You just need to add a new column that has a list of all dates between start and end days, and then expand them into new rows. Finally perform a group by operation on the new date column and count the number of rows, which is the number of active campaigns on a given day.
Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!
Hi @Juan_Quikin5 ,
You could try something like this in Power Query. Please open a blank query--> Advanced editor-->Remove any existing code and copy and paste the below code.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTRNzTSNzIwMlJQUNJRMjZA4YYEOesaKsXqRCsZGcElgOKGxvoGhiCeMVSRkVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t, EndDate = _t, CampaignID = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}, {"CampaignID", type text}}),
#"Added Date" = Table.AddColumn(#"Changed Type", "Date", each {Number.From([StartDate])..Number.From([EndDate])}),
#"Expanded Date" = Table.ExpandListColumn(#"Added Date", "Date"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"Date"}, {{"CampaignCount", each Table.RowCount(_), Int64.Type}})
in
#"Grouped Rows"
Input
Output
You just need to add a new column that has a list of all dates between start and end days, and then expand them into new rows. Finally perform a group by operation on the new date column and count the number of rows, which is the number of active campaigns on a given day.
Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
75 | |
63 | |
52 | |
47 |
User | Count |
---|---|
215 | |
85 | |
61 | |
61 | |
60 |