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
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
Plain & simple, what a game changer 🙂