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 risks in doing so?Cheers,Ben

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. OldSource = 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"}}),NewSource = 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.

Changing data connection from ODBC to SQL | Microsoft Fabric Community

16 Replies

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
  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
    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.
v-qiuyu-msft
Community Support
9 years ago
Hi Greg_Deckler,

Greg_Deckler provides detail information, good job.

Just want to prompt you, assume the visuals are already created in the report, the modified query SQL needs to return the same column fields as the original query ODBC. Otherwise you need to recreate visuals in the report. See my post in this similar thread: https://community.powerbi.com/t5/Integrations-with-Files-and/Change-the-data-source-from-on-premise-SQL-Server-to-SQL-Azure/td-p/101785

If you want to utilize the report in Power BI service, you need to create a SQL data source under on-premise data gateway, then configure the dataset to use that gateway.

Best Regards,
Qiuyun Yu
- Anonymous
  Not applicable
  9 years ago
  Greg_Deckler
  
  Heres the query, first is the old ODBC, and the second set is the new SQL. For some reason I lose all of the "applied steps" in the Query, all the renaming and duplicating of columns etc. Error prompt "token EOF expected" and it points to the last line in the query, no matter what iswritten on that last line. What am I doing wrong?
  
  OLD (ODBC)
  
  let
  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"}}),
  #"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Document Date", "Document Date - Copy"),
  #"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"Document Date - Copy", "Calendar Year"}}),
  #"Inserted Year" = Table.AddColumn(#"Renamed Columns1", "Year", each Date.Year([Calendar Year]), type number),
  #"Renamed Columns2" = Table.RenameColumns(#"Inserted Year",{{"t_cwoc_c", "Office"}}),
  #"Duplicated Column1" = Table.DuplicateColumn(#"Renamed Columns2", "Document Date", "Document Date - Copy"),
  #"Extracted Month Name" = Table.TransformColumns(#"Duplicated Column1", {{"Document Date - Copy", each Date.MonthName(_), type text}}),
  #"Renamed Columns3" = Table.RenameColumns(#"Extracted Month Name",{{"Document Date - Copy", "Month"}}),
  #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns3",{"Calendar Year"}),
  #"Renamed Columns4" = Table.RenameColumns(#"Removed Columns",{{"Year", "Cal Year"}, {"Month", "Cal Month"}, {"t_amth_1", "CAD Amount"}, {"t_balh_1", "CAD Balance"}, {"t_year", "GL Year"}, {"t_prod", "GL Period"}}),
  #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns4",{"t_line", "t_tdoc", "t_docn", "t_lino", "t_trec", "t_ccur", "t_rate_1", "t_rate_2", "t_rate_3", "t_ratf_1", "t_ratf_2", "t_ratf_3", "t_rade", "t_amnt", "t_amti", "t_amth_2", "t_amth_3", "t_ratd", "t_rtyp", "t_vatc", "t_cvat", "t_vata", "t_vati", "t_vath_1", "t_vath_2", "t_vath_3", "t_svam", "t_svah_1", "t_svah_2", "t_svah_3", "t_lpdt", "t_lapa", "t_lapi", "t_laph_1", "t_laph_2", "t_laph_3", "t_did1", "t_dc1a", "t_dc1i", "t_dc1h_1", "t_dc1h_2", "t_dc1h_3", "t_did2", "t_dc2a", "t_dc2i", "t_dc2h_1", "t_dc2h_2", "t_dc2h_3", "t_did3", "t_dc3a", "t_dc3i", "t_dc3h_1", "t_dc3h_2", "t_dc3h_3", "t_pada", "t_padi", "t_padh_1", "t_padh_2", "t_padh_3", "t_baco", "t_baca_1", "t_baca_2", "t_baca_3", "t_basi", "t_cdam_1", "t_cdam_2", "t_cdam_3", "t_tore_1", "t_tore_2", "t_tore_3", "t_dued", "t_refr", "t_ccrs", "t_cpay", "t_otyp", "t_oinv", "t_paym", "t_proj", "t_orno", "t_prob", "t_prop", "t_pref", "t_bank", "t_lett", "t_post", "t_pdat", "t_balc", "t_balh_2", "t_balh_3", "t_bala", "t_bahc_1", "t_bahc_2", "t_bahc_3", "t_stat", "t_btno", "t_vaty", "t_vatp", "t_crep", "t_liqd", "t_adrs", "t_lamt", "t_step", "t_rcpt", "t_doub", "t_cain", "t_lvat", "t_typa", "t_doca", "t_pfbp", "t_ofbp", "t_text", "t_arrq", "t_chck", "t_bkno", "t_equp", "t_unit", "t_asmt", "t_isup", "t_btno_c", "t_seqn_c", "t_fpid_c", "t_pdam_c", "t_tiat_c", "t_ipay_c", "t_itic_c", "t_iwrt_c", "t_ipid_c", "t_orin_c", "t_insq_c", "t_inti", "t_dorg_c", "t_ptyp_c", "t_pdoc_c", "t_reut_c", "t_ppit_c", "t_ppis_c", "t_ppin_c", "t_flno_c", "t_sord_c", "t_Refcntd", "t_Refcntu", "t_ccnt_c", "t_ffno_c", "t_fldt_c", "t_tnrn_c", "t_ccpt_c"}),
  #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"Document Number", type text}}),
  #"Added Custom" = Table.AddColumn(#"Changed Type", "DOCUMENT TYPE & NUMBER", each [Document Type]&" - "&[Document Number]),
  #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Document Type", "Document Number", "DOCUMENT TYPE & NUMBER", "Invoice to BP", "Document Date", "CAD Amount", "CAD Balance", "GL Year", "GL Period", "Office", "Cal Year", "Cal Month"}),
  #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ([Document Type] = "SE1" or [Document Type] = "SIM" or [Document Type] = "SP1" or [Document Type] = "SR1" or [Document Type] = "SS1")),
  #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"CAD Balance", Int64.Type}}),
  #"Grouped Rows" = Table.Group(#"Changed Type1", {"DOCUMENT TYPE & NUMBER", "Invoice to BP"}, {{"Balance", each List.Sum([CAD Balance]), type number}, {"Min Date", each List.Min([Document Date]), type datetime}, {"Max Date", each List.Max([Document Date]), type datetime}}),
  #"Extracted Date" = Table.TransformColumns(#"Grouped Rows",{{"Min Date", DateTime.Date}, {"Max Date", DateTime.Date}}),
  #"Filtered Rows2" = Table.SelectRows(#"Extracted Date", each [Balance] = 0),
  #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows2", each [Min Date] > #date(2013, 12, 31))
  in
  #"Filtered Rows1"
  
  New(SQL)
  
  let
  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"}}),
  #"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Document Date", "Document Date - Copy"),
  #"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"Document Date - Copy", "Calendar Year"}}),
  #"Inserted Year" = Table.AddColumn(#"Renamed Columns1", "Year", each Date.Year([Calendar Year]), type number),
  #"Renamed Columns2" = Table.RenameColumns(#"Inserted Year",{{"t_cwoc_c", "Office"}}),
  #"Duplicated Column1" = Table.DuplicateColumn(#"Renamed Columns2", "Document Date", "Document Date - Copy"),
  #"Extracted Month Name" = Table.TransformColumns(#"Duplicated Column1", {{"Document Date - Copy", each Date.MonthName(_), type text}}),
  #"Renamed Columns3" = Table.RenameColumns(#"Extracted Month Name",{{"Document Date - Copy", "Month"}}),
  #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns3",{"Calendar Year"}),
  #"Renamed Columns4" = Table.RenameColumns(#"Removed Columns",{{"Year", "Cal Year"}, {"Month", "Cal Month"}, {"t_amth_1", "CAD Amount"}, {"t_balh_1", "CAD Balance"}, {"t_year", "GL Year"}, {"t_prod", "GL Period"}}),
  #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns4",{"t_line", "t_tdoc", "t_docn", "t_lino", "t_trec", "t_ccur", "t_rate_1", "t_rate_2", "t_rate_3", "t_ratf_1", "t_ratf_2", "t_ratf_3", "t_rade", "t_amnt", "t_amti", "t_amth_2", "t_amth_3", "t_ratd", "t_rtyp", "t_vatc", "t_cvat", "t_vata", "t_vati", "t_vath_1", "t_vath_2", "t_vath_3", "t_svam", "t_svah_1", "t_svah_2", "t_svah_3", "t_lpdt", "t_lapa", "t_lapi", "t_laph_1", "t_laph_2", "t_laph_3", "t_did1", "t_dc1a", "t_dc1i", "t_dc1h_1", "t_dc1h_2", "t_dc1h_3", "t_did2", "t_dc2a", "t_dc2i", "t_dc2h_1", "t_dc2h_2", "t_dc2h_3", "t_did3", "t_dc3a", "t_dc3i", "t_dc3h_1", "t_dc3h_2", "t_dc3h_3", "t_pada", "t_padi", "t_padh_1", "t_padh_2", "t_padh_3", "t_baco", "t_baca_1", "t_baca_2", "t_baca_3", "t_basi", "t_cdam_1", "t_cdam_2", "t_cdam_3", "t_tore_1", "t_tore_2", "t_tore_3", "t_dued", "t_refr", "t_ccrs", "t_cpay", "t_otyp", "t_oinv", "t_paym", "t_proj", "t_orno", "t_prob", "t_prop", "t_pref", "t_bank", "t_lett", "t_post", "t_pdat", "t_balc", "t_balh_2", "t_balh_3", "t_bala", "t_bahc_1", "t_bahc_2", "t_bahc_3", "t_stat", "t_btno", "t_vaty", "t_vatp", "t_crep", "t_liqd", "t_adrs", "t_lamt", "t_step", "t_rcpt", "t_doub", "t_cain", "t_lvat", "t_typa", "t_doca", "t_pfbp", "t_ofbp", "t_text", "t_arrq", "t_chck", "t_bkno", "t_equp", "t_unit", "t_asmt", "t_isup", "t_btno_c", "t_seqn_c", "t_fpid_c", "t_pdam_c", "t_tiat_c", "t_ipay_c", "t_itic_c", "t_iwrt_c", "t_ipid_c", "t_orin_c", "t_insq_c", "t_inti", "t_dorg_c", "t_ptyp_c", "t_pdoc_c", "t_reut_c", "t_ppit_c", "t_ppis_c", "t_ppin_c", "t_flno_c", "t_sord_c", "t_Refcntd", "t_Refcntu", "t_ccnt_c", "t_ffno_c", "t_fldt_c", "t_tnrn_c", "t_ccpt_c"}),
  #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"Document Number", type text}}),
  #"Added Custom" = Table.AddColumn(#"Changed Type", "DOCUMENT TYPE & NUMBER", each [Document Type]&" - "&[Document Number]),
  #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Document Type", "Document Number", "DOCUMENT TYPE & NUMBER", "Invoice to BP", "Document Date", "CAD Amount", "CAD Balance", "GL Year", "GL Period", "Office", "Cal Year", "Cal Month"}),
  #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ([Document Type] = "SE1" or [Document Type] = "SIM" or [Document Type] = "SP1" or [Document Type] = "SR1" or [Document Type] = "SS1")),
  #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"CAD Balance", Int64.Type}}),
  #"Grouped Rows" = Table.Group(#"Changed Type1", {"DOCUMENT TYPE & NUMBER", "Invoice to BP"}, {{"Balance", each List.Sum([CAD Balance]), type number}, {"Min Date", each List.Min([Document Date]), type datetime}, {"Max Date", each List.Max([Document Date]), type datetime}}),
  #"Extracted Date" = Table.TransformColumns(#"Grouped Rows",{{"Min Date", DateTime.Date}, {"Max Date", DateTime.Date}}),
  #"Filtered Rows2" = Table.SelectRows(#"Extracted Date", each [Balance] = 0),
  #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows2", each [Min Date] > #date(2013, 12, 31))
  in
  #"Filtered Rows1"
  dbo_ttfacr200140
  - Greg_Deckler
    Community Champion
    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.

Forum Discussion

Changing data connection from ODBC to SQL

16 Replies

Related content

Connect to Data Source bug or change?

Data Days | Connect

Parameterized connections in Data pipelines

change data source of live connected report

Destination Connection dropdown changed