Forum Discussion
Changing data connection from ODBC to SQL
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.
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
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 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?
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"I GOT IT WORKING!
Thanks for your help Greg_Deckler