Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Dynamic Column Update

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...
Need Help
Tutorial Requests
  • jennratten's avatar
    jennratten
    4 years ago

    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.

jennratten's avatar
jennratten
Icon for Super User rankSuper User
4 years ago

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"