EXCEL unable to recognize data hierarchy from PBI dataset

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.