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 venug20,

 

Could you please mark the proper answers as solutions?

 

Best Regards,

Dale

venug20's avatar
venug20
Resolver I
8 years ago

Hi Dale,

 

Thanks for Respond to my post.

 

As per your previous reply.

 

The Old data source is "Access Database", Using Microsoft tool i converted to SQL DB. in that, What are column in table from old Data Source, same columns reflect in SQL aslo there is no chance to miss any columns.

 

Regards

Venu

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 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
        7 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