Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Changing data connection from ODBC to SQL

Hi everyone, forgive me I'm but a novice here haha.  I have a client that wants to switch the data connection they currently have in PBI from ODBC to SQL. What is involved with this? are there any...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    9 years ago

    The last line should just be :

     

    #"Filtered Rows1"

     

    Also, for some reason you changed the names of some of your steps but are still referring to the old steps.

     

    Old

    Source = Odbc.DataSource("dsn=Dataext", [HierarchicalNavigation=true]),
data1234_Database = Source{[Name="data1234",Kind="Database"]}[Data],
dbo_Schema = data1234_Database{[Name="dbo",Kind="Schema"]}[Data],
ttfacr200140_Table = dbo_Schema{[Name="ttfacr200140",Kind="Table"]}[Data],
#"Renamed Columns" = Table.RenameColumns(ttfacr200140_Table,{{"t_ttyp", "Document Type"}, {"t_ninv", "Document Number"}, {"t_itbp", "Invoice to BP"}, {"t_docd", "Document Date"}}),

    New

    Source = Sql.Databases("177.101.5.67\Dataext"),
data1234 = Source{[Name="data1234"]}[Data],
dbo_ttfacr200140 = data1234{[Schema="dbo",Item="ttfacr200140"]}[Data],
#"Renamed Columns" = Table.RenameColumns(ttfacr200140_Table,{{"t_ttyp", "Document Type"}, {"t_ninv", "Document Number"}, {"t_itbp", "Invoice to BP"}, {"t_docd", "Document Date"}}),

    I'm not sure why you changed your query up like that but you are referring to steps that do not exist in your query and that's going to cause problems. The idea was to swap out the Source line and the Source line only.

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

Generally not. First, copy your PBIX file to a new file. Then, create a new query that connects via SQL. Then, open the Advanced Editor and copy the Source line only. Open your ODBC query and replace the Source line with the one that you copied. May also need the Navigation line, but that's the general idea.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Thanks so much for the reply. Can you break that down even further to a step by step?

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      9 years ago

      Make a copy of your PBIX file so that everything is safe.

       

      Create a query to your SQL database and make sure it works.  This query doesn't need to do anything really except connect to the database table that you want to start with. All you want is the Source line from this query.

       

      Open up Advanced Editor, you should see the code behind the query, it will start with:

       

      let
      Source = ...,

       

      You want to copy that Source line.

       

      Then, go into your ODBC query and open up Advanced Editor. Just paste over the Source line in this query.

       

      You may also need the Navigate line. So in Advanced Editor (Edit Queries and then View | Advanced Editor) you might see:

       

      let

      Source = ...,

      Navigate = ...,

       

      Just copy both of those lines. It will depend on your SQL query though and if you are using the navigation of the query editor or a SQL query statement, those kinds of specifics but that's the general idea.

       

      If you can create the queries and paste the code here from both (from Advanced Editor), I can probably get it sorted for you or be more specific.

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        I will have to take you up on that offer! Thanks!