Power query - Change column header to display date function results

I am using Power Query - Excel 2013.  I need to be able to replace the column header name to display the current date - 3 days and the remaining columns to add 1 day per column .

ie.  today = 11/2/ 2015
Column4 =10/30/2015
column 5 = 10/31/2015
column6 - 11/1/2015
etc.
what is the function needed to replace the Column header to display the correct date

found function need help with syntax:

    #"Renamed Columns" = Table.RenameColumns(US1_Sheet,{{"Column1", "FC"}, {"Column2", "FCfilter"}, {"Column3", "DataLabel"}, {"Column4",Date.AddDays(DateTime.FromText(datetime), -3)}}),

Promoting headers does not change the column headers to display the correct date range.

K
Karen SchaeferBI ANALYSTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
If you are using a Table then you cannot use formulas in the Header Row,

You can work around this by hiding the header row in the Table Design options and having a dummy header row displayed that contains the formulas. See the example & hide the Header Row in the Table
Headers.xlsx
0
Karen SchaeferBI ANALYSTAuthor Commented:
Thanks for the sample, however, this is not a Power Query example.  I am looking on examples on how to work within DAX and Power Query.

such as this example I found, however, I need to determine Column count and then set the column headings with a date fill based on value in column6 (current date-1)

so how do I modify this code to handle the remaining column headers to display the date for each day going accross all column headers?

#"Today" = Date.From ( DateTime.LocalNow() ),
#"Three Days Ago Label" = Date.ToText ( Date.AddDays ( #"Today", -3 ) ),
#"Two Days Ago Label" = Date.ToText ( Date.AddDays ( #"Today", -2 ) ),
#"One Day Ago Label" = Date.ToText ( Date.AddDays ( #"Today", -1 ) ),
#"Renamed Columns" = Table.RenameColumns(US1_Sheet,{{"Column1", "FC"}, {"Column2", "FCFilter"}, {"Column3", "DataLabel"}, {"Column4", #"Three Days Ago Label"}, {"Column5", #"Two Days Ago Label"}, {"Column6", #"One Day Ago Label"} })

Open in new window

0
Roy CoxGroup Finance ManagerCommented:
Attach an example workbook
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Karen SchaeferBI ANALYSTAuthor Commented:
Things have changed a little - turns out the equation works create exempt that I miscalculated what needs to be done - instead I need to either figure out how to retrieve a start date from the original data (cell C8) and be able to use that as the basis for the calculation  of dates for the remaining columns.

Or get the dates to be set as headers.  which currently fails.  I will promote the non-date columns to the header but the date fields failsamplePivotData.xlsx

I have attached a sample xls with pivot.  Please help

Here is my current code:
 let GetValue=(DateUsed) => 
    let
      name = Excel.CurrentWorkbook(){[Name=DateUsed]}[Content],
      value = name{0}[Column3]
    in
      value
    in GetValue

let[b] '<<<<<<<<<<<<<<<<<<<<<< Dies Here[/b]
   // #"Today" = Date.From ( DateTime.LocalNow() ),
  //  #"Three Days Ago Label" = Date.ToText ( Date.AddDays ( GetValue, -3 ) ),
   // #"Two Days Ago Label" = Date.ToText ( Date.AddDays ( GetValue, -2 ) ),
  //  #"One Day Ago Label" = Date.ToText ( Date.AddDays ( GetValue, -1 ) ),
   // #"CurrentDay" = Date.ToText ( Date.AddDays ( GetValue, 0 ) ),
    #"Day1 Label" = Date.ToText ( Date.AddDays ( GetValue, 1 ) ),
    #"Day2 Label" = Date.ToText ( Date.AddDays ( GetValue, 2 ) ),
    #"Day3 Label" = Date.ToText ( Date.AddDays ( GetValue, 3 ) ),
    #"Day4 Label" = Date.ToText ( Date.AddDays ( GetValue, 4 ) ),
    #"Day5 Label" = Date.ToText ( Date.AddDays ( GetValue, 5 ) ),
    #"Day6 Label" = Date.ToText ( Date.AddDays ( GetValue, 6 ) ),
    #"Day7 Label" = Date.ToText ( Date.AddDays ( GetValue, 7 ) ),
    #"Day9 Label" = Date.ToText ( Date.AddDays ( GetValue, 8 ) ),

    Source = Excel.Workbook(File.Contents("\\ant\dept\sandop\BIGGulpReport\INBOUND_FORECAST_SUMMARYReport.xlsx"), null, true),
    US_BIG_GULP_STRING_Sheet = Source{[Item="US_BIG_GULP_STRING",Kind="Sheet"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(US_BIG_GULP_STRING_Sheet,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", Int64.Type}, {"Column10", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> "Network" and [Column1] <> "Non Sortable" and [Column1] <> "Pantry" and [Column1] <> "Small Sortable" and [Column1] <> "Sortable" and [Column1] <> "Specialty") and ([Column2] = "New Vendor Freight ?")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column1", "FC"}, {"Column2", "FC Filter"}, {"Column3", GetValue}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Renamed Columns",{{"Column4", #"Day1 Label"}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Renamed Columns1",{{"Column5", #"Day2 Label"}}),
    #"Renamed Columns3" = Table.RenameColumns(#"Renamed Columns2",{{"Column6", #"Day3 Label"}}),
    #"Renamed Columns4" = Table.RenameColumns(#"Renamed Columns3",{{"Column7", #"Day4 Label"}}),
    #"Renamed Columns5" = Table.RenameColumns(#"Renamed Columns4",{{"Column8", #"Day5 Label"}}),
    #"Renamed Columns6" = Table.RenameColumns(#"Renamed Columns5",{{"Column9", #"Day6 Label"}}),
    #"Renamed Columns7" = Table.RenameColumns(#"Renamed Columns6",{{"Column10", #"Day7 Label"}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns7", {"FC", "FC Filter"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Open in new window

0
Roy CoxGroup Finance ManagerCommented:
Are you using my suggestion but need to select a data for the startdate?
0
Karen SchaeferBI ANALYSTAuthor Commented:
No thanks for your time found another solution - points award for time.
0
Roy CoxGroup Finance ManagerCommented:
Thanks. It would be interesting to see the solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.