title: Query works in Power Query Editor but not in Power BI
body: <p>I am just getting started with Power BI and want to be able to compare report performance between Direct Query and Import mode.  What I am about to describe may be possible by performing data manipulations in Import mode.  That is not what this question is about.</p>
  <p>Using Power BI against SQL Server 2016.</p>
  <p>I am working with data that is, unfortunately, pre-pivoted.  In order to unpivot it, I am doing something like this:</p>
  <pre><code>;
  with a as (
         SELECT pd.[ProjectDollarsId]
         , pd.[VersionId]
         , pd.[ProjectId]
         , pd.[FundTypeId]
         , pd.[ApprovalId]
         , pd.[PriorDollar]
         , pd.[LastYearDollar]
         , pd.[ThisYearDollar]
         , pd.[NextYearDollar]
         , pd.[FutureDollar]
         , pd.[TotalDollar]
         , v.[Year] as VersionYear
  
         FROM [ProjectDollars] pd
           inner join [Version] v on v.VersionId = pd.VersionId
  
         where v.[Year] &gt; 2016
  ), 
  b as (
         select ProjectDollarsId
         , VersionId
         , ProjectId
         , ProjectPhaseId
         , FundTypeId
         , ApprovalId
         , VersionYear
         , DollarsYear
         , Dollars
  
         from a pvt
         unpivot (
                Dollars for DollarsYear in (
                       PriorDollar, 
                       LastYearDollar, 
                       ThisYearDollar, 
                       NextYearDollar, 
                       FutureDollar)
                ) as unpvt
  )
  
  select ProjectDollarsId
  , VersionId
  , ProjectId
  , FundTypeId
  , ApprovalId
  , case DollarsYear
         when 'PriorDollar'    then VersionYear - 2
         when 'LastYearDollar' then VersionYear - 1
         when 'ThisYearDollar' then VersionYear
         when 'NextYearDollar' then VersionYear + 1
         when 'FutureDollar'   then VersionYear + 2
      else 0
    end as [Year]
  , Dollars
  from b
  </code></pre>
  <p>This works fine in Power Query Editor.  I get a resulting table with correct column names and data to preview.</p>
  <p>After I click Close and Apply, Power BI says:</p>
  <blockquote>
  <p>Microsoft SQL: Incorrect syntax near ';'. Incorrect syntax near ')'.</p>
  </blockquote>
  <p>What do I need to do to work around this?</p>