Forum Discussion
dates as column names
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
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.
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
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).
Plain & simple, what a game changer 🙂