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

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! 

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    9 years ago

    Hi Anonymous,

     

    Did the issue solved now? If it is, would you please mark helpful replies as answers so we can close the thread?

     

    Best Regards,
    Qiuyun Yu

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      I will be trying a few of the solutions today/tomorrow and then i will be able to close the thread

      • v-qiuyu-msft's avatar
        v-qiuyu-msft
        Community Support
        9 years ago

        Hi Anonymous,

         

        May I know any result after you try the suggestions?

         

        Best Regards,
        Qiuyun Yu