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
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
I 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!Sorry for the delayed response. I was out of the office for a few days this week. This is really close! I am so appreciative of your help. A couple of questions.
- I see that the values are limited to just five instances. Is this simply due to you using a sample of five weeks to create the template you posted to my question? If so, would this just automatically expand out if I link it up to my data that has all 53 weeks populated with percentages?
- One thing that will start to happen as the current year progresses is the response curve will flow into next year. I only see current fields created. How would what you provided handle when the 53 week response curve flows from current year into next year?
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