asked on

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

Roy Cox

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

Karen Schaefer

ASKER

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

ASKER CERTIFIED SOLUTION

Roy Cox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Karen Schaefer

ASKER

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

Roy Cox

Are you using my suggestion but need to select a data for the startdate?

Karen Schaefer

ASKER

No thanks for your time found another solution - points award for time.

Roy Cox

Thanks. It would be interesting to see the solution.