The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
I was able to connect to data model in excel powerpivot via the import function. There are three tables in the data model, one sourced via a direct connection via Oracle provider client in the data model. The two others are tables within the excel spreadsheet created as linked tables in the data model.
When I open up the file, it tells me there is an "Expression.Error: Token Indentifier expected.
Please help, I really don't want to recreate all my visuals!!
Thanks,
Rachel
it's probably because Power Query in Excel can reference the workbook it's based in, PowerBI cannot do that - you need to provide the address of that Excel file
When you go to the Power Query in Excel and click the Advanced Editor view you probably see something like this
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column", Int64.Type}}) in #"Changed Type"
which worked fine in Excel, but will not work in PowerBI
In PowerBI you would need something like this
let Source = Excel.Workbook(File.Contents("C:\file.xlsx"), null, true), Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Column", Int64.Type}}) in #"Changed Type"
Hello,
Thanks for your help--I'm not sure how to apply that to what I'm actually seeing in advanced editor--the teradata.database portion...the odd thing is that I'm not see in there any code that references the calculated fields/items that I created in the data model...maybe that is the issue, is that it just imported the code to get the data directly, but didn't do anything to link to the calculated items in the data model that was just brought in automatically during the first import but can't find those to refresh? Is there a way around that? The main reason I imported was so i didn't need to rebuild the calculated items...
let
Source = #!" let#(cr)#(lf) Source = Teradata.Database(""epicprd"", [Query=""With APPT AS #(#)(lf)( #(#)(lf)SELECT#(#)(lf)mv.appt_dept_id#(#)(lf),mv.appt_dept_name#(#)(lf),mv.appt_visit_type_id#(#)(lf),mv.appt_prov_type_group#(#)(lf),sum(mv.appt_sched_via_ops_cnt) as OPS_SCHED_CNT#(#)(lf),sum(mv.appt_row_cnt) as ALL_SCHED_CNT#(#)(lf),sum(mv.appt_complete_cnt) as ALL_COMP_CNT#(#)(lf)#(#)(lf)from clarityrpt.appt_stats_dtl_mv mv#(#)(lf)where TRUNC(mv.appt_made_time) between to_date('2019-06-01', 'yyyy-mm-dd') and to_date('2019-07-31', 'yyyy-mm-dd')#(#)(lf) #(#)(lf)and mv.appt_chng_row_yn = 'N'#(#)(lf)and mv.appt_complete_cnt > 0#(#)(lf)#(#)(lf)group by#(#)(lf)mv.appt_dept_id#(#)(lf),mv.appt_dept_name#(#)(lf),mv.appt_visit_type_id#(#)(lf),mv.appt_prov_type_group#(#)(lf)),#(#)(lf)#(#)(lf)VT AS #(#)(lf)(#(#)(lf)SELECT#(#)(lf)x.effective_end#(#)(lf),x.dep_5_dept_active#(#)(lf),x.ser_65_department_id#(#)(lf),x.dep_dot2_department_name#(#)(lf),x.prc_dot1_prc_id#(#)(lf),x.prc_dot2_prc_name#(#)(lf),x.dep_4315_rpt_grp_sixteen#(#)(lf),x.dep_4309_family_of_care#(#)(lf),pt.APPT_PROV_TYPE_GROUP as prov_typ_grp#(#)(lf),count(distinct x.ser_dot1_prov_id) as Prov_Count#(#)(lf)from d8813.elem_1_vt_and_rest_hx_tbl_tst x#(#)(lf)left join clarity_ser s on x.ser_dot1_prov_id = s.prov_id#(#)(lf)left join view_cstm.access_config_prov_type pt on s.provider_type_c = pt.PROV_TYPE_C#(#)(lf)where x.effective_end is null#(#)(lf)group by#(#)(lf)x.effective_end#(#)(lf),x.dep_5_dept_active#(#)(lf),x.ser_65_department_id#(#)(lf),x.dep_dot2_department_name#(#)(lf),x.prc_dot1_prc_id#(#)(lf),x.prc_dot2_prc_name#(#)(lf),x.dep_4315_rpt_grp_sixteen#(#)(lf),x.dep_4309_family_of_care#(#)(lf),pt.APPT_PROV_TYPE_GROUP#(#)(lf))#(#)(lf)#(#)(lf)SELECT * FROM VT #(#)(lf)LEFT JOIN APPT #(#)(lf)on VT.prc_dot1_prc_id = APPT.appt_visit_type_id#(#)(lf)and VT.ser_65_department_id = APPT.appt_dept_id#(#)(lf)and VT.prov_typ_grp = APPT.appt_prov_type_group""]),#(cr)#(lf) #""Renamed Columns"" = Table.RenameColumns(Source, {{""EFFECTIVE_END"", ""EFFECTIVE_END""}, {""DEP_5_DEPT_ACTIVE"", ""DEP_5_DEPT_ACTIVE""}, {""SER_65_DEPARTMENT_ID"", ""SER_65_DEPARTMENT_ID""}, {""DEP_DOT2_DEPARTMENT_NAME"", ""DEP_DOT2_DEPARTMENT_NAME""}, {""PRC_DOT1_PRC_ID"", ""PRC_DOT1_PRC_ID""}, {""PRC_DOT2_PRC_NAME"", ""PRC_DOT2_PRC_NAME""}, {""DEP_4315_RPT_GRP_SIXTEEN"", ""DEP_4315_RPT_GRP_SIXTEEN""}, {""DEP_4309_FAMILY_OF_CARE"", ""DEP_4309_FAMILY_OF_CARE""}, {""PROV_COUNT"", ""PROV_COUNT""}, {""PROV_TYP_GRP"", ""PROV_TYP_GRP""}, {""APPT_DEPT_ID"", ""APPT_DEPT_ID""}, {""APPT_DEPT_NAME"", ""APPT_DEPT_NAME""}, {""APPT_VISIT_TYPE_ID"", ""APPT_VISIT_TYPE_ID""}, {""APPT_PROV_TYPE_GROUP"", ""APPT_PROV_TYPE_GROUP""}, {""OPS_SCHED_CNT"", ""OPS_SCHED_CNT""}, {""ALL_SCHED_CNT"", ""ALL_SCHED_CNT""}, {""ALL_COMP_CNT"", ""ALL_COMP_CNT""}}),,#(cr)#(lf) #""Changed Type"" = Table.TransformColumnTypes(#""Renamed Columns"", {{""EFFECTIVE_END"", type datetime}, {""DEP_5_DEPT_ACTIVE"", type text}, {""SER_65_DEPARTMENT_ID"", type number}, {""DEP_DOT2_DEPARTMENT_NAME"", type text}, {""PRC_DOT1_PRC_ID"", type text}, {""PRC_DOT2_PRC_NAME"", type text}, {""DEP_4315_RPT_GRP_SIXTEEN"", type text}, {""DEP_4309_FAMILY_OF_CARE"", type text}, {""PROV_COUNT"", type number}, {""PROV_TYP_GRP"", type text}, {""APPT_DEPT_ID"", type number}, {""APPT_DEPT_NAME"", type text}, {""APPT_VISIT_TYPE_ID"", type text}, {""APPT_PROV_TYPE_GROUP"", type text}, {""OPS_SCHED_CNT"", type number}, {""ALL_SCHED_CNT"", type number}, {""ALL_COMP_CNT"", type number}})#(cr)#(lf)in#(cr)#(lf) #""Changed Type"""
in
Source
you only need to chagne the Excel queries, the Teradata one should work correctly, unless it's somehow based on the Excel input
Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.
User | Count |
---|---|
157 | |
106 | |
96 | |
83 | |
75 |
User | Count |
---|---|
153 | |
137 | |
131 | |
81 | |
61 |