Forum Discussion

jcbutts's avatar
jcbutts
Helper I
6 years ago
Solved

Replace Column Headers with current dates

Hi,

 

I'm new to this:

 

I'm trying to replace column headers with the current week in one column and iterate from there.  For example, I have columns that say "week 1", "week 2", "week 3".....etc.  I would like to replace "week 1" with the date of the current week (start or end doesn't matter), "week 2" as a week from now, "week 3" as two weeks from now, and so on.  Is that possible?

Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    Hello jcbutts 

     

    I recognized that I got you wrong. So week 1 is always the current week.

    I fixed this now. Check out this one if it works

    let
	Source = #table
	(
		{"week 1","week 2","week 3","other data","abc","week 52"},
		{
			{"","","","","",""}
		}
	),
    ColumnNames = Table.ColumnNames
    (
        Source
    ),
    TransformWeekInDate = List.Transform
    (
        ColumnNames,
        (row)=> try
        if Text.Contains(row,"week") or Text.Contains(row,"Week") then 
            Text.From(Date.AddDays(Date.AddDays( Date.From(DateTime.FixedLocalNow()), Date.DayOfWeek(DateTime.FixedLocalNow())*-1), (Number.From(Text.Replace(row, "week ", ""))-1)*7))
            else 
            row
        otherwise
        row
    ),
    RenameColumnHeader = Table.RenameColumns
    (
        Source,
        List.Zip({ColumnNames,TransformWeekInDate})
    )
in
	RenameColumnHeader

     

    Jimmy

14 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    Hello jcbutts 

     

    this solution should be really dynamic and working for 2020. The column names have to be "week " and the number. 

    let
	Source = #table
	(
		{"week 1","week 2","week 3","other data","abc","week 52"},
		{
			{"","","","","",""}
		}
	),
    ColumnNames = Table.ColumnNames
    (
        Source
    ),
    TransformWeekInDate = List.Transform
    (
        ColumnNames,
        (row)=> try
        if Text.Contains(row,"week") or Text.Contains(row,"Week") then 
            Text.From(Date.AddDays(#date(2019,12,30), (Number.From(Text.Replace(row, "week ", ""))-1)*7))
            else 
            row
        otherwise
        row
    ),
    RenameColumnHeader = Table.RenameColumns
    (
        Source,
        List.Zip({ColumnNames,TransformWeekInDate})
    )
in
	RenameColumnHeader

     

    Copy paste this code to the advanced editor to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query, or I could create a custom function what makes it easier to apply if you are not used that much to power query.

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

    • jcbutts's avatar
      jcbutts
      Helper I
      6 years ago

      Hi Jimmy,

       

      The code seems to be what I'm looking for.  However, when I run it, "week 1"= last week (12/30/19), when it should equal this week (somewhere between 1/5/2020 and 1/11/2020).  How do I fix that?

       

      Also, what are the steps to incorporate into my own code?  I apologize in advance....I'm very new at this.  I tried taking everything in your code below the "source" line and putting it in at the bottom of my code.  The result is a small matrix with a link that says "Table".  I click on that link and it reopens my data.....but the dates aren't changed.  Mine looks like this:

       

      let
      Source = Excel.Workbook(File.Contents("V:\\Forecast and Inventory Planning_US.xlsx"), null, true),
      #"US_Forecast and Inventory Plann_Sheet" = Source{[Item="US_Forecast and Inventory Plann",Kind="Sheet"]}[Data],
      #"Removed Top Rows" = Table.Skip(#"US_Forecast and Inventory Plann_Sheet",1),
      #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true])
      in
      #"Promoted Headers"

       

      Any direction is appreciated.  

      • Jimmy801's avatar
        Jimmy801
        Community Champion
        6 years ago

        Hello jcbutts 

         

        I recognized that I got you wrong. So week 1 is always the current week.

        I fixed this now. Check out this one if it works

        let
	Source = #table
	(
		{"week 1","week 2","week 3","other data","abc","week 52"},
		{
			{"","","","","",""}
		}
	),
    ColumnNames = Table.ColumnNames
    (
        Source
    ),
    TransformWeekInDate = List.Transform
    (
        ColumnNames,
        (row)=> try
        if Text.Contains(row,"week") or Text.Contains(row,"Week") then 
            Text.From(Date.AddDays(Date.AddDays( Date.From(DateTime.FixedLocalNow()), Date.DayOfWeek(DateTime.FixedLocalNow())*-1), (Number.From(Text.Replace(row, "week ", ""))-1)*7))
            else 
            row
        otherwise
        row
    ),
    RenameColumnHeader = Table.RenameColumns
    (
        Source,
        List.Zip({ColumnNames,TransformWeekInDate})
    )
in
	RenameColumnHeader

         

        Jimmy

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi jcbutts 

    try smth

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Week1 = _t, Week2 = _t, Week3 = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{"Week1", Text.From(Date.StartOfWeek(Date.From(DateTime.LocalNow())))}),
    #"Renamed Columns2" = Table.RenameColumns(#"Renamed Columns",{"Week2", Text.From(Date.StartOfWeek(Date.AddWeeks(Date.From(DateTime.LocalNow()),1)))})
in
    #"Renamed Columns2"

     

    do not hesitate to give a kudo to useful posts and mark solutions as solution

    LinkedIn

     

    • jcbutts's avatar
      jcbutts
      Helper I
      6 years ago

      Thanks.  I already have some modifications prior to this step, so how do I change your code to accept the existing source?  I get an error in the advanced editor saying the variable named 'source' is already defined.

      • az38's avatar
        az38
        Community Champion
        6 years ago

        jcbutts 

        you should replace source here

         #"Renamed Columns" = Table.RenameColumns(Source,{"Week1", Text.From(Date.StartOfWeek(Date.From(DateTime.LocalNow())))}),

        to the name of your previous step,like you could see here (#"Renamed Columns" is a name of previous step)

            #"Renamed Columns2" = Table.RenameColumns(#"Renamed Columns",{"Week2", Text.From(Date.StartOfWeek(Date.AddWeeks(Date.From(DateTime.LocalNow()),1)))})

         

        do not hesitate to give a kudo to useful posts and mark solutions as solution