Formatting the code from IT to match Power Query / Power BI

user_15991529 0
Viewed 40

Could someone please confirm what code language is it and how do I edit it to fit with Power BI function to import data from SQL Database please?

Got this from IT person but when I try to paste it into Power Query it gives error messages.

SELECT distinct  Z.Territory , (z.AccountNumber          )  as AccountNumber , (a.AccountType                     ) as AccountType , (z.CompanyName                        ) as CompanyName , (z.AccountNumber        ) as AccountNumber , (z.CompanyName        ) as CompanyName , z.SubscriptionReference               ,z.SubscriptionID , (isnull(z.serialnumber,r.SerialNumber)             ) as SerialNumber ,case when (rpc.billingperiodalignment) =  'AlignToCharge' then (z.product) else  isnull(r.ProductDescription,z.product) end as Description  , (r.ProductVersion                                                      ) as ProductVersion , (r.CoverExpiryDate                           ) as CoverExpiryDate , (z.SubscriptionStatus                       ) as  SubscriptionStatus ,z.SubscriptionVersion 
--, MAX(z.RenewalTerm)             AS RenewalTerm , (z.[SubscriptionTermType]) as [SubscriptionTermType] , (case when z.[UnitofMeasure] = 'Desktop Users' then z.[Quantity] end) as 'Desktop Users' , (z.billingperiod                                              ) as BillingPeriod ,(rpc.BillingPeriodAlignment) BillingPeriodAlignment ,(z.[ContractEffectiveDate]) as  ContractEffectiveDate ,(z.TermEndDate) as  TermEndDate
powerbi powerquery sql
1 Answers

It's SQL, but it's invalid, so you'll need to get it fixed. It looks like it got cut off. When it's valid you should be able to paste it into a SQL Server Management Studio query window and test it.

To use it in Power Query use Value.NativeQuery, like this;

let
    Source = Sql.Database("localhost", "adventureworks2017"),
    Query = "
    select * 
    from Sales.SalesOrderHeader
    ",
    Data = Value.NativeQuery(Source, Query, null, [EnableFolding=true])
in
    Data
user_72977000
Related