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

jcbutts's avatar
jcbutts
Helper I
6 years ago

Hi Az38,

 

Sorry, that's not making sense to me.  My code looks like this:

 

let
Source = Excel.Workbook(File.Contents("V:\Dpmts\Forecast.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]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ASIN", type text}})
in
#"Changed Type"

 

I assume I need to keep the line calling out source= my excel workbook.  Do I put in a new "let"-> "in" sequence or does your code get nested in to what I have?

az38's avatar
az38
Community Champion
6 years ago

jcbutts 

try

let
Source = Excel.Workbook(File.Contents("V:\Dpmts\Forecast.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]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ASIN", type text}}),
    #"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)))}),
    #"Renamed Columns3" = Table.RenameColumns(#"Renamed Columns2",{"Week3", Text.From(Date.StartOfWeek(Date.AddWeeks(Date.From(DateTime.LocalNow()),2)))})
in
    #"Renamed Columns3"

 

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

LinkedIn

  • 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.