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".....et...
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

Mariusz's avatar
Mariusz
Community Champion
6 years ago

Hi jcbutts 

 

You can try this as an alternative.

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 [Id = _t, #"week 1" = _t, #"week 2" = _t, #"week 3" = _t]),
    transformColumnNames = List.Transform( 
        List.Select( 
            Table.ColumnNames( Source ), 
            ( i ) => Text.Contains( i, "week" ) 
        ), 
        (i) => let 
            extractWeek = Number.From( Text.Replace( i, "week ", "" ) ),
            weekStart = Date.StartOfWeek( DateTime.LocalNow() ),
            newName = 
                Text.From( 
                    Date.From( 
                        Date.AddWeeks( weekStart, extractWeek - 1 ) 
                    ) 
                )
        in  
            { i, newName }
    ),
    #"Renamed Columns" = Table.RenameColumns(Source, transformColumnNames )
in
    #"Renamed Columns"

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

 

jcbutts's avatar
jcbutts
Helper I
6 years ago

I tried all of these.  For the first solution, I got an error saying the colunmn "Week 1" wasn't found.  My actual column header is "Week 1- Mean Forecast" so I modified the code to match (checking a few different iterations for the spacing between the dash and the words) but it all came up with the same error...."Week 1 - Mean Forecast" not found.

 

For the other two solutions, the code was accepted, but it resulted in a table with one line.   The headers were things like "Name" and "Hidden", and the data was just the name of my data file and the type.

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi jcbutts 

     

    I've adjusted the code to accommodate the different Column name structure.

     

    Please see the attached file.

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

     

     

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    Hello jcbutts 

     

    in my solution I've stated that the code is to show you how the solution works, nothing more, and that is has to be adapted to your query by copying and pasting or creating a customer function. It wasn't stated in your first request that we can have a negative number. I've proposed a dynamic solution for year 2020 considering that the week 1 is the one starting on 30.12.19 exactly how it was requested

     

    Jimmy