dates as column names

[wparker5932]

My excel spreadsheet uses dates as column header names but the data in the columns is just numbers.  When I import into Power BI the header is converted to a number.  How can I keep the header as a date but the column data as a number?

[v-qiuyu-msft]

Hi @wparker5932,

Based on my test in Power BI desktop version 2.42.4611.701, after we get data from the Excel, the column headers will keep as dates. Please check attached files.

About the issue you mentioned, it would be better if you can share the sample Excel file for our analysis.

Best Regards,
Qiuyun Yu

[wparker5932]

Actually, I found a work-around by first highlighting the Excel data, with headers, and converting to a table within the spreadsheet.  Once imported into Power BI (which converts the dates to text) I used the Query, highlight the columns with the dates as text headers, select theTransform tab, and using the unpivot columns command to switch the headers to a column of data and changing the data type from Text to Date. Once done I can use all of the date filters.

Having Power BI keep the format would help save me from doing the above.

[MarcelBeug]

Nice that it is working for you, but I still don't understand how you managed to get dates as numbers.

I would appreciate if you can share that information.

[wparker5932 · Accepted solution]

From a basic Excel spreadsheet where the column headers are formated as Dates highlight the data area and insert as a table. Use the Power BI to Get Data bringing the Excel table into Power BI.  This conerts the column headings from a date format to a text format. Highlight the columns with the Power BI Query Editor and select the Transform tab and click on the Unpivot Columns. Now Date column headers appear as a column named Attribute and the data under those Date columns is noted in the adjacent column as Values. Highlight the Attribute column and right-click, selecting Change Type and the Date.  Now rename the column Header to Date and the Value header to Hours.  I can now display and filter data using all of the Date filters in Power BI.  It sounds complicated but once I started it all went smoothly.

1. Basic Spreadsheet

2. Convert data to a table3. Get data from Excel to Power BI4. Highlight Date Columns and Unpivot5. Change Type to Date

[MarcelBeug]

Thanks for your comprehensive explanation.

Please watch this small video showing:

that I get dates when importing an Excel sheet in Power BI,

how I still manage to get numbers in my headers (well, it's still text but it looks like numbers) and

an alternative to unpivot and turn the numbers into dates (with a 2 step change data type : first to whole number, then to date).

[MarcelBeug]

Whatever I try: if I have an Excel sheet with dates as headers, these are dates in Power BI as well.

So can you eloborate on your exact scenario: can you share the code from Power BI, what exactly are you importing: a table, a named range, a worksheet?

Are you importing 1 Excel file or files from a folder?

Are you directly importing from Excel (xlsx file) or e.g. via csv or other file format?