Date behavior in PBI Service Dataset

[Anonymous]

Hi, im new using PBI. 

I created a simple excel file with a date column (monthly based since 2011), and a couple columns more with some info. 

The file was saved in Sharepoint site, and imported in PBI Service as a Dataset just to easy update method. 

In PBI Desktop I imported the Dataset and everything is ok, except than the date doesnt work as expected, it doesnt show the options of "Year", "Quarter", "Month"... The dataset in PBI service automaticaly created a couple of columns more with Month and Year. 

I need to made some quartely calculations and graphs, but cannot find the way. 

[Anonymous]

I can't edit Queries in this file.... It's because is a Dataset on Power BI service or it's because is a Excel File in sharepoint?

[AkhilAshok]

Looks like you are connected live to PBI Dataset. So there is nothing much you can do, unless you can import the Power BI file with the dataset. If someoneelse developed the Dataset, talk to the person and get the Data type of date changed.

[Anonymous · Accepted solution]

I own the dataset (is in PowerBI service) and I own the Excel file connected with the dataset. 

I found the solution, import directly from Excel in Sharepoint to PBI Desktop without using PBI Service Dataset import, and edit the queries. 

[Anonymous]

That's odd, not sure why.

See if you can change the data type without having to go into Power Query.

1. Click on the "Date" field

2. Go to your top ribbon and go to Modeling > Data Type

3. Change Data Type to Date

[Anonymous]

Nope, Date are the only field that can't be changed in Formatting menu.... 😕

[Anonymous]

This could be because Power BI doesn't recognize the values in that column as dates.

1. Examine the column in detail by going to PowerQuery. To get into PowerQuery, click on the three ellipses next to your table name (in this case "Demanda") and click Edit query.

2. Find your column name in the table (in this case "Date"). Click the header to highlight the column and in the top ribbon go to Transform > Data Type and change it to Date. This should cause the icon next to "Date" to look like a tiny calendar.

3. In the ribbon, go to File > Close & Apply.

And it should start working as intended.

[AkhilAshok]

Just make sure the table is formatted as Date in Power Query Editor. Just right click on the date column in Query Editor, and select Change Type -> Date