Forum Discussion
Assistance Needed: Automating Distribution of Projected Responders in Power BI
Hi krichmond
Sorry for the late reply. You can try the following code. I expand it to 53*2 weeks range. See Response Projected Master New query in the attachment.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlFwVNJRMjIwMgFS5kBsbGpkqBSrg1XeAogNjYwtLHApsARiM0MDnAYYGoAIQ0tLc5wqDEGEibmxqVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Year = _t, Week = _t, #"Projected Responders" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Year", Int64.Type}, {"Week", Int64.Type}, {"Projected Responders", Int64.Type}}), // You can replicate steps starting from here, or paste the following code to your query and modify step names accordingly #"Added Custom" = Table.AddColumn(#"Changed Type", "CY Week", each {1..53*2}), #"Expanded CY Week" = Table.ExpandListColumn(#"Added Custom", "CY Week"), #"Added Custom1" = Table.AddColumn(#"Expanded CY Week", "MatchingWeek", each let vWeek = [CY Week]-[Week]+1 in if vWeek > 0 and vWeek < 54 then vWeek else -1), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"CY Week", Int64.Type}, {"MatchingWeek", Int64.Type}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Product", "Year", "MatchingWeek"}, #"Response Percent Master", {"Product", "Year", "Week"}, "Response Percent Master", JoinKind.LeftOuter), #"Expanded Response Percent Master" = Table.ExpandTableColumn(#"Merged Queries", "Response Percent Master", {"Response Percentage"}, {"Response Percentage"}), #"Added Custom2" = Table.AddColumn(#"Expanded Response Percent Master", "Value", each [Projected Responders]*[Response Percentage]), #"Replaced Value" = Table.ReplaceValue(#"Added Custom2",null,0,Replacer.ReplaceValue,{"Value"}), #"Sorted Rows" = Table.Sort(#"Replaced Value",{{"Year", Order.Ascending}, {"Product", Order.Ascending}, {"Week", Order.Ascending}, {"CY Week", Order.Ascending}}), #"Added Prefix" = Table.TransformColumns(#"Sorted Rows", {{"CY Week", each if _ < 54 then "CY Week " & Text.From(_, "en-US") else "NY Week " & Text.From(_ - 53 , "en-US"), type text}}), #"Removed Other Columns" = Table.SelectColumns(#"Added Prefix",{"Product", "Year", "Week", "Projected Responders", "CY Week", "Value"}), #"Rounded Up" = Table.TransformColumns(#"Removed Other Columns",{{"Value", Number.RoundUp, Int64.Type}}), #"Pivoted Column" = Table.Pivot(#"Rounded Up", List.Distinct(#"Rounded Up"[#"CY Week"]), "CY Week", "Value") in #"Pivoted Column"Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
Hi krichmond
For the first question, the answer is Yes. Because I'm not able to access the full sample data, I make up only five weeks data for testing. But it also works when the data is expanded to 53 weeks. You can replicate steps starting from #"Added Custom" step.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlFwVNJRMjIwMgFS5kBsbGpkqBSrg1XeAogNjYwtLHApsARiM0MDnAYYGoAIQ0tLc5wqDEGEibmxqVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Year = _t, Week = _t, #"Projected Responders" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Year", Int64.Type}, {"Week", Int64.Type}, {"Projected Responders", Int64.Type}}),
// You can replicate steps starting from here, or paste the following code to your query and modify step names accordingly
#"Added Custom" = Table.AddColumn(#"Changed Type", "CY Week", each {1..53}),
#"Expanded CY Week" = Table.ExpandListColumn(#"Added Custom", "CY Week"),
#"Added Custom1" = Table.AddColumn(#"Expanded CY Week", "MatchingWeek", each [CY Week]-[Week]+1),
#"Merged Queries" = Table.NestedJoin(#"Added Custom1", {"Product", "Year", "MatchingWeek"}, #"Response Percent Master", {"Product", "Year", "Week"}, "Response Percent Master", JoinKind.LeftOuter),
#"Expanded Response Percent Master" = Table.ExpandTableColumn(#"Merged Queries", "Response Percent Master", {"Response Percentage"}, {"Response Percentage"}),
#"Added Custom2" = Table.AddColumn(#"Expanded Response Percent Master", "Value", each [Projected Responders]*[Response Percentage]),
#"Replaced Value" = Table.ReplaceValue(#"Added Custom2",null,0,Replacer.ReplaceValue,{"Value"}),
#"Added Prefix" = Table.TransformColumns(#"Replaced Value", {{"CY Week", each "CY Week " & Text.From(_, "en-US"), type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Added Prefix",{"Product", "Year", "Week", "Projected Responders", "CY Week", "Value"}),
#"Rounded Up" = Table.TransformColumns(#"Removed Other Columns",{{"Value", Number.RoundUp, Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Rounded Up", List.Distinct(#"Rounded Up"[#"CY Week"]), "CY Week", "Value")
in
#"Pivoted Column"
For the second question, sorry I don't understand "One thing that will start to happen as the current year progresses is the response curve will flow into next year." well. Can you provide some example data to help describe the desired outcome?
In the current solution, you can find that I add two columns "CY Week" and "MatchingWeek" for help. This may have an affect for the result in next year but I'm not sure now.
Regards,
Jing
Your first response worked when I tested it, thank you! For your second response, sorry for the confusion. Let me try to make things a little clearer. We are trying to forecast responses coming in from marketing campaigns using a rolling 53 week window of time. So say a marketing campaign goes out in early January in the current year, most of the 53 week response curve would fall in the current year but a small piece would technically flow into next year. However, as time goes on in the current year, more and more of the response curve would flow into the following year. Then when the close of the current year looms, the majority of the response curve would technically flow into the following year since they happened so late in the year. So using your Response workbook, the Product A, 2024, Week 11 row should run into NY since it is starting at week 11 in the current year. That is why I was trying to show CY weeks 1 - 53 and NY weeks 1-53. So the percentages run in a row, weeks 1 - 53 at which point the marketing campaign is considered mature and won't change. But the response curve results could flow across years like (just dummy data but hopefully helps paint the picture):
| CY Week 47 | CY Week 48 | CY Week 49 | CY Week 50 | CY Week 51 | CY Week 52 | CY Week 53 | NY Week 1 | NY Week 2 | NY Week 3 |
| 10 | 9 | 8 | 7 | 6 | 5 | 4 | 3 | 2 | 1 |
I know it is confusing though. Hopefully this explaination helps. If not, let me know.