Forum Discussion

jujiro-eb's avatar
jujiro-eb
Helper I
6 years ago
Solved

M expression(s) not working in SQL queries during refreshes from Portal (Help)

In my Power BI report, I was computing dates using M-expressions.  I used these dates in the sql queries without any issue for more than six months since the report's inception.  All of a sudden, aro...
Need Help
  • jujiro-eb's avatar
    jujiro-eb
    6 years ago

    Apparently, some recent software update at the PBI portal broke something.  
    Symptoms: If enhanced dataset mode is enabled in your PBIX, M expressions stop working.

    Microsoft has recognized this to be an issue and will provide a fix on 7/21/2020.


    "Awareness

    Power BI Customers using the Enhanced dataset metadata preview feature may see refresh not complete with the following message "Credentials are required to connect to the File source". As a workaround, users can disable the "enhanced dataset metadata (Preview)" feature in Power BI Desktop. Engineers are working on a fix and expect it to be deployed by end-of-day 07/21/2020."
lbendlin's avatar
lbendlin
Super User
6 years ago

p_days is not a text, it is a number.

 

let
    p_days = 5,
    Source = Date.FromText("2020-06-26"),
    new_date = Date.AddDays(Source, p_days)
in
    Date.ToText(new_date, "MM/dd/yyyy")
  • jujiro-eb's avatar
    jujiro-eb
    Helper I
    6 years ago

    The parameter p_days is not the problem as such.  

    add_days = Number.FromText(p_days),

     

    You can see that it was converted to number before use.  Even the expression you have provided results into SQL credential error.  As I mentioned in my original post, this issue occurs only when refresh is done on the portal.  Local PBI Desktop works as expected.

    • lbendlin's avatar
      lbendlin
      Super User
      6 years ago

      Does the server have different locale settings? Try specifying the dates as YYYY-MM-DD.

      • jujiro-eb's avatar
        jujiro-eb
        Helper I
        6 years ago

        lbendlin 

         

        The server hosting the portal is controlled by Azure.  I have no idea what locale settings it is set.  Assuming that everything on US East 2, it is safe to assume that it may be be using MM/dd/yyyy format.  Besides, the text to date conversion is being supplied with a format.