EXCEL unable to recognize data hierarchy from PBI dataset

[dccosta]

Hi,

I've been trying to link an EXCEL file to my PBI dataset as a way to circumvent EXCEL's limited ability to store large sets of data, but for some reason, it does not identify the Dataset date field as a date field.

This field is formatted as date in Power BI, and is imported in the same format DD/MM/YYYY to EXCEL (already changed the regional settings to make that work), but for some reason EXCEL doesn't automatically group it in Months, Years, etc, because it doesn't recognize it as a date field.

Anyone knows how to solve this?

[rubayatyasmin · Accepted solution]

Hi, @dccosta 

You can try to fix this problem in several ways. Here are some steps you can follow:

1. Change Date Format in Excel: Excel may not recognize your dates if they are not in a format that Excel understands. Try to change the format to one Excel recognizes.

   • Right-click on your column and choose 'Format Cells'.
   • In the Format Cells box, click on the 'Number' tab and select 'Date' in the category list.
   • Select a date format that Excel recognizes, like MM/DD/YYYY.
   • Click 'OK' to apply the new format.

2. Text to Columns: Excel has a function called 'Text to Columns' that can be used to split a column of text in a way that Excel can recognize.

   • Click on the column with the dates.
   • In the 'Data' tab, click 'Text to Columns'.
   • Choose 'Delimited' and click 'Next'.
   • Select the delimiter that separates your dates (it can be a slash, dot, dash, or space). Click 'Next'.
   • Select 'Date' and the format your dates are in (DMY, MDY, YMD). Click 'Finish'.
   • Your dates should now be recognized by Excel.

3. Excel Date Function: If Excel is still not recognizing your dates, you can use Excel's DATE function to create a new column with recognized dates.

   • Write =DATE(RIGHT(A1, 4), MID(A1, 4, 2), LEFT(A1, 2)) in the cell next to your first date (assuming A1 is the cell where your first date is).
   • This formula will create a date with the format YYYY-MM-DD. Drag the formula to all your cells.
   • Your dates should now be recognized by Excel.

4. Use Power Query: Excel's Power Query can be used to change the data type of a column to a date.

   • In the 'Data' tab, click 'From Table/Range'.
   • Select your date column and click 'Transform'.
   • In the 'Data Type' dropdown, select 'Date'.
   • Click 'Close & Load'.
   • Your dates should now be recognized by Excel.

These steps should help Excel recognize your dates. Note that these solutions are temporary, and you will have to repeat them every time you load new data. If you want a permanent solution, consider changing the format of your dates in your source data or creating a macro that applies these steps every time you load new data.

[dccosta]

Thanks!

The issue is that I'm basically working with an external link to my Power BI Dataset, so I don't really have a way to use EXCEL functions or formatting options on the actual columns.

I have worked out a way to do this by creating a specific hierarchy within Power BI which then gets recognized by EXCEL as a hierarchy, when I link the dataset. For some reason (as far as I understand, historical one?) EXCEL does not retain the date hierarchy of existing fields in the Power BI Dataset and recognize it automatically.