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!