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 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…

Need Help

8 Replies

  • Seward12533's avatar
    Seward12533
    Solution Sage
    8 years ago

    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's avatar
      venug20
      Resolver I
      8 years ago

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

        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

    • 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
        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