Forum Discussion

venug20's avatar
venug20
Resolver I
8 years ago

Sale & Marketing Data Source Connect to SQL Server Error

Hi experts, I am trying to connect data source “SQL Server” from advanced editor, which I was download from Microsoft “Sales & Marketing Sample.PBIX” file. My Server name is : DESKTOP-F125T7K And ...
Need Help
v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi Venu,

 

Can the SQL Query run successfully in other tools like SSMS? Can you share the file? Are the blue parts just input error?

Source = Sql.Database("DESKTOP-F125T7K", "PowerBI", [Query="SELECT #(lf) p.ProductID,#(lf) 
p.Product,#(lf) p.Category,#(lf) Segment,#(lf) m.[ManufacturerID],#(lf) 
IIF(p.Manufacturer='Maximus', 'VanArsdel', p.Manufacturer) Manufacturer, #(lf) p.IsCompete#(lf)
FROM [dbo].[bi_Product] p, #(lf) (#(lf)  select distinct [ManufacturerID], #(lf)Manufacturer#(lf)  
from [PowerBI].[dbo].[bi_Sentiment]#(lf) ) m#(lf)where m.Manufacturer = p.Manufacturer"]),

Best Regards,

Dale

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi venug20,

     

    How did get the dataset and import it into SQL Server? There should be two tables [Product] and [Sentiment]. I only changed the Server name and DB name. It works. Can you share some snapshots of the status of tables in SQL Server? Since it's a public dataset, can you share the dataset?

    let
    Source = Sql.Database("dale2016", "fortest", [Query="SELECT #(lf) p.ProductID,#(lf) p.Product,#(lf) p.Category,#(lf) Segment,#(lf) m.[ManufacturerID],#(lf) IIF(p.Manufacturer='Maximus', 'VanArsdel', p.Manufacturer) Manufacturer, #(lf) p.IsCompete#(lf)FROM [Product] p, #(lf) (#(lf)  select distinct [ManufacturerID], #(lf)Manufacturer#(lf)  from [Sentiment]#(lf) ) m#(lf)where m.Manufacturer = p.Manufacturer"]),
    #"Renamed Columns" = Table.RenameColumns(Source, {{"Manufacturer", "Manufacturer"}, {"Category", "Category"}, {"Segment", "Segment"}, {"Product", "Product"}, {"ProductID", "ProductID"}, {"IsCompete", "IsCompeteHide"}, {"ManufacturerID", "ManufacturerID"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Manufacturer", type text}, {"Category", type text}, {"Segment", type text}, {"Product", type text}, {"ProductID", Int64.Type}, {"IsCompeteHide", type text}, {"ManufacturerID", Int64.Type}})
in
    #"Changed Type"

    Sale_Marketing_Data_Source_Connect_to_SQL_Server_Error

     

     

    Best Regards,

    Dale