Changing data connection from ODBC to SQL

[Anonymous]

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

[v-qiuyu-msft]

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

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]

@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 · Accepted solution]

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.

[Anonymous]

@Greg_Deckler PBI automatically generated the first three lines of that new query when i made the sql connection, thats why I figured it should overwrite the lines above "#" in the old odbc query. 

So do you mean to say that the only line i need to over write is "

Source = Odbc.DataSource("dsn=Dataext", [HierarchicalNavigation=true]),

From the old query, with

Source = Sql.Databases("177.101.5.67\Dataext"),

 and replace all 

dbo_ttfacr200140 

in the new query with

ttfacr200140_Table

 From the old query?

[Anonymous]

@Greg_Deckler
I just replaced the source lines only, that being the top line. Now the applied steps are back, however I get a new error.

"Expression.Error: The key didn't match any rows in the table.
Details:
Key=Record
Table=Table"

[Anonymous]

I get this error also ...

"Expression.Error: The key didn't match any rows in the table.
Details:
Key=Record
Table=Table"

... what did you do about it?

[Anonymous]

If you share the code with me from the query editor i can probably rewrite it for ya. 

[Anonymous]

I GOT IT WORKING! 

Thanks for your help @Greg_Deckler

[Greg_Deckler]

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]

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

[Greg_Deckler]

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]

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

[v-qiuyu-msft]

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]

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

[v-qiuyu-msft]

Hi @Anonymous,

May I know any result after you try the suggestions?

Best Regards,
Qiuyun Yu

[Anonymous]

Okay, so now i have made the SQL connection successfully however I loose all the "applied steps" that were previously taken to shape the old ODBC connected table. The query is still there in the editor however i get a Unexpected EOF prompt that points to the bottom line below "in" How do I retain the old formating??