Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Direct Query or Import for changing for correcting the data

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? 

Need Help

12 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        amitchandak
        Super User
        6 years ago

        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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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 textDate, format is textDate2 createdDate2 created

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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's avatar
    Anonymous
    Not applicable
    6 years ago

    @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"

    1.PNG

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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