Forum Discussion

jujiro-eb's avatar
jujiro-eb
Icon for Helper I rankHelper 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."
Smauro's avatar
Smauro
Icon for Solution Sage rankSolution Sage
6 years ago

If you haven't changed any credentials between Service and Desktop, it could be that they've added some extra restrictions.

Could you try faking it as a param and see if it works?

Date.ToText(
    Date.AddDays(
        Date.FromText("2020-06-26"),
        Number.From(p_days)
    ),
    "MM/dd/yyyy"
)
meta
[
    IsParameterQuery = true,
    Type = "Text",
    IsParameterQueryRequired = false
]

 

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

    Smauro 

     

    You had my hopes up so much!  ğŸ˜€

     

    Unfortunately, it did not work.  Same, credential error.  Once again, PBI Desktop evaluates the parameter (which is now an expression,) correctly.  But thanks though, I did not think of that.  I had tried other things like passing parameters through a text file to the same fate.