Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wparker5932
Frequent Visitor

dates as column names

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?

1 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 Spreadsheet1. Basic Spreadsheet

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

View solution in original post

6 REPLIES 6
v-qiuyu-msft
Community Support
Community Support

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.

 

w1.PNGw2.PNG

 

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

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 

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.

Specializing in Power Query Formula Language (M)

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 Spreadsheet1. Basic Spreadsheet

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

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

 

Specializing in Power Query Formula Language (M)
MarcelBeug
Community Champion
Community Champion

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?

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.