Forum Discussion
Helper IVAssistance Needed: Automating Distribution of Projected Responders in Power BI
I have two main data tables structured as follows:
Response Percent Master: This table includes columns for "Product & Week & Year" (composite key), "Product", "Year", "Week", and "Response Percentage". It holds the response percentages for different products across weeks and years.
Response Projected Master: This table contains columns for "Product & Week & Year" (composite key), "Product", "Year", "Week", and "Projected Responders". It captures the projected number of responders for products over various weeks and years. Currently, it includes data for the current and next year, with plans to update it annually for subsequent years.
The Excel workbook provided serves as a data source resembling Power BI's data model, including a "Target Layout" tab illustrating the desired report format.
In the "Target Layout", considering row 2 for "Product A" during week 7, initial columns (F-K) show zeros, reflecting time periods before week 7. From week 7 onwards, the distribution of "Projected Responders" is spread over 53 weeks (columns L to BL), using a formula that references "Projected Responders" and adjusts based on data from the "Response Percent Master". Once the 53 weeks of formula occurs, everything after that is zeros.
For instance, week 7 (column L) shows a calculation based on the 3,521 projected responders and a response percentage of 0.02105% from "Response Percent Master", resulting in a value of 0.74, which is rounded up to 1. Subsequent columns (M, N) show increased values (72, 637) corresponding to the rising response percentages (2.03816%, 18.08182%) for the respective weeks.
Implementing this dynamic distribution of projected responders across weeks in Power BI proves challenging, especially automating the distribution across columns based on varying start weeks and response percentages from the "Response Percent Master" table.
Seeking guidance on replicating this Excel-based logic in Power BI, particularly automating the week-over-week distribution of "Projected Responders" using response percentages, while accommodating different starting weeks and product percentages.
Response Percent Master TabResponse Projected Master TabTarget Layout Tab
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!
10 Replies
Hi krichmond
Sorry I'm not able to see the excel due to network restriction. Is it possible to show the desired output and the logic in a screenshot and paste some sample data directly in the reply? Thank you.
Best Regards,
Jing
krichmondI just updated the post to include some photos. Let me know if you need anything else. Thank you for letting me know.
Hi krichmond
I did this with Power Query. Please download the pbix file to see detailed steps in Power Query Editor. Feel free to ask if you don't understand any step.
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
- krichmond
Anonymous - Just wanted to check in to see if you have any ideas after I provided the additional information above?
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!- krichmond
Anonymous this works as expected in the "Transform Data" screen!!! Thank you! So close!!! When I click "Close & Apply" I get the following error message. Any idea on how to resolve that?