Dynamic Column Update

[Anonymous]

Hi,

I have a summary table, where I am taking weekly summary. 

In this table I have columns for each week: 1,2,3,....53

I am trying to build a Replace Value query, where we get week number from "Date.WeekOfYear(DateTime.LocalNow)", so which ever column name match this value of current week number will be replaced by the sum of Sales column, if the weeknumber doesnot match the column name, then leave the column values as it is. 

If there is any other way to achive this, please suggest.

Any help is appriciated.  

Thank you in advanced. 

Best Regards

[jennratten]

Hello - here is how you can accomplish this using Power Query.

let
    Source = List.Zip ( { List.Transform ( {1..53}, each "Column" & Number.ToText(_) ),List.Repeat ( {"abc123"}, 53 ) } ),
    #"Converted to Table1" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Extracted Values2" = Table.TransformColumns(#"Converted to Table1", {"Column1", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values2", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Transposed Table" = Table.Transpose(#"Split Column by Delimiter"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    varTargetColumn = "Column"&Number.ToText(Date.WeekOfYear(DateTime.FixedLocalNow())),
    #"Replaced Value" = Table.ReplaceValue(#"Promoted Headers", each Record.Field ( _, varTargetColumn ),"zzzzz",Replacer.ReplaceText,{varTargetColumn})
in
    #"Replaced Value"

[Anonymous]

Hello @jennratten,

Thank you very much for your reply and efforts. 

I tried your suggestion and the Replace querys work. The issue now is, once the varTargetColumn value changes, the old changed value changes back to abc123. 

I mean, once the week number changes from 21 to 22, the column for 22 becomes zzz and the column for 21 is changed back to abc123. 

I want both column to be zzz. Essentially, replace if the coulm value matches, else leave the values as it is(Not as it is in the source, the values might have changed since what was in the source. For example, we might have some value for week 20, I dont want it to change it back to what it was before the replace query updated it)

Let me know if this makes sense. 

Thank you for all your help. 

Best Regards

[jennratten]

Where are the new values coming from?  Is there another table with the new values and dates?

[Anonymous]

@jennratten ,

My new values are comming from another table's column = 'Project'[forecast]

There is no date column, the forecast keeps changing, so I am trying to get a snapshot summary of forecast for each week. That is, save the forecast for week 20, As the week ends, the Summary value of forecast for week 20 is locked, and we move on to update week 21 forecast. 


Please let me know if you need any other info. 

Thank you for your help!

[jennratten · Accepted solution]

The purpose of Power Query is to transform existing source data.  If you are trying to include historical forecast values then those should be present in the source data somewhere.  It sounds like they are not.  The best option I think would be to either create a file that contains historical forecast amounts with their applicable dates (this could easily be done with Power Automate), or use Power Query to read all of the weekly files and aggregate the weekly amounts.

[Anonymous]

@jennratten 

How if we add a new column every week? 

As the week change from 20 to 21, we add an new column of 21 and update its value. Will this help in restoring column 20's updated values?

Please let me know.

Thank you!