Sale & Marketing Data Source Connect to SQL Server Error

[venug20]

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 My Database is : PowerBI

let

    Source = Sql.Database(".", "IP", [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 [SalesAndMarketing].[Product] p, #(lf) (#(lf)  select distinct [ManufacturerID], #(lf)Manufacturer#(lf)  from [IP].[SalesAndMarketing].[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"

Below one I modified…..

let

    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"]),

    #"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"

I am getting error like

DataSource.Error: Microsoft SQL: Invalid column name 'IsCompete'.

Details:

    DataSourceKind=SQL

    DataSourcePath=desktop-f125t7k ;PowerBI

    Message=Invalid column name 'IsCompete'.

    Number=207

    Class=16

How can I resolve this… Pls help on this…

[v-jiascu-msft]

Hi @venug20,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[venug20]

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]

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

[venug20]

@v-jiascu-msft

I got this file from https://docs.microsoft.com/en-us/power-bi/sample-sales-and-marketing

After that i want to edit from my "Power BI Desktop", it's through error like above....

[v-jiascu-msft]

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"

Best Regards,

Dale

[Seward12533]

What does it look like if you just connect to the data source on your localSQL Serever as a new SQL query? 

GET DATA -> SQL 

After you oenter your server you should see a list of DATABASES and/or TABLES after you select a TABLE or VIEW it will bring some sample data in with a SELECT ALL query. 

You can then prep you data in Power Query. Whats nice is PowerBI does Query folding so when you actually run it will push a filtered query up to the SQL server to pull in only the data you need. 

[venug20]

Thanks for reply Seward,

I am very new to Power BI....

i want to do practice with "Sales & Marketing sample.PBIX", can get from microsoft sample provided.

I am trying to edit with power query, in this situation it is asking "Edit Permission". when click on this button. i got above code. i am trying to change with my localhost SQL Server. while doing this process i am getting error.

How overcome this situation, i don't understand...

Pls help on this....

[v-jiascu-msft]

Hi @venug20,

If you want to change the data source to a new Server, the new data source should have the same tables as the old ones (mainly the same structure.)  

As we can see from the error message, you localhost SQL Server doesn't have a column "IsCompete". Please check out. 

Best Regards,

Dale