Direct Query or Import for changing for correcting the data

[Anonymous]

Hi, 

I am new to the community and to Power BI! I have uploaded one data set from Google BigQuery as Direct Query (not Import) mode. I immediately noticed that I faced some limitations. For example I want to change data format from Text to Date (or find a way that Power BI revise this column in a way it will be converted to date)

I got the error the it is not possible in Direct Query mode (Pls see below). Is there any way for me to reach what I want w/o changing the mode? Or you recommend to switch the mode? If I switch the mode and my data set is updated in Google Cloud is my report updated as well automatically? 

[Anonymous]

@V-pazhen-msft

Please see below screenshot. I can create three columns (Year, Month, day) sepreately. But the problem as you see is I don't have same number of data rows and I guess this is the main problem.

I used below formulas for Year, Month, and Day:

Year = LEFT(SMHI[Date],4)

Month = RIGHT(SMHI[Date],2)

Day = "1"

[Anonymous]

@Anonymous 

Is it possible to share that sample pbix with me to test, or show how it your date column(Text type) looks like. I am really curious why that happens. 

Thank you 

Paul

[Anonymous]

@Anonymous 

You cannot change the column type or do those tranformation under Direct Query mode, I would suggest you create a date column with DAX. 

Date2 = DATE(LEFT([Date],4),RIGHT([Date],2),1)

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks a lot @V-pazhen-msft for the help!

I have tried. I got error though as you see below. Is there any adjustement needed for the format?

Date, format is textDate2 created

[Anonymous]

@Anonymous 

It works with my sample under DQ mode. It is fine with text date. Try check the original date column see if there is invalid values in the text strings.

Regards
Paul

[Anonymous]

@V-pazhen-msft

Paul: everything you said is logical to me and I followed them. There is also no problem with original data as I checked. But for some reason I can't achieve my goal. I am getting the same error as above! 😞

[amitchandak]

@Anonymous , you will not be able to change the data type in Power Query. But you will be able to do that in column tools. Looking at the format I doubt It will take it as a date.  But you should able to split and create date both in M and Dax as new column. But data type change will happen in column properties

[Anonymous]

Thanks @amitchandak for the quick resposne. 

I tried to split (and create new column), but I again got the error message. Please see below: 

[amitchandak]

@Anonymous , I checked out split is not supported Direct query.

"01-" & Text.End([Order No],2) & "-" &Text.Start([Order No],4) //did not work

Where you able to change data type using column property 

[Anonymous]

@amitchandak then the only way for me is to switch to Import mode, right? 

[amitchandak]

@Anonymous , what is data you are getting it is month year like 202001 ?

now try this [Month Year]*100 + 01 . I was able to create one such column . You will also be able to create a date table in power bi there you can have date key like this.

If you have data only at month level we can create month table , time intelligence will not work , we can use rank to do that.

refer:

https://youtu.be/24arfrD3Qzk

https://youtu.be/7Jc3D4iaTqs

[Anonymous]

@amitchandak Yes, you are right! 202001 means 2020 and month 1

I am sorry, not fully follow. Where do I do this? "now try this [Year of the month] * 100 + 01."