Hi everyone,
I have a few tables brought into Power BI via Direct Query with Spark and I also have an Excel file. I was able to connect these tables to each other and if I create a visual using columns from the tables that use Direct Query, I have no issues. But if I combine a Direct Query table with the Excel table, I receive an error message saying: "OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression.." Could you please help me out?
If it helps, here's the entire error message:
Feedback Type:
Frown (Error)
Timestamp:
2020-10-13T12:15:55.9988854Z
Local Time:
2020-10-13T15:15:55.9988854+03:00
Session ID:
03efe329-f098-49a6-83ba-604aa2d14f0e
Release:
August 2020
Product Version:
2.84.981.0 (20.08) (x64)
Error Message:
OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression..
OS Version:
Microsoft Windows NT 10.0.18363.0 (x64 en-US)
CLR Version:
4.7 or later [Release Number = 528040]
Peak Virtual Memory:
39.8 GB
Private Memory:
1.68 GB
Peak Working Set:
1.81 GB
IE Version:
11.1082.18362.0
User ID:
831f0b8e-b85c-4bb9-9edd-716285d4d14d
Workbook Package Info:
1* - en-GB, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.
Telemetry Enabled:
True
Snapshot Trace Logs:
C:\Users\hagherlacher.o\Microsoft\Power BI Desktop Store App\FrownSnapShot571008031.zip
Model Default Mode:
Composite
Model Version:
PowerBI_V3
Is Report V3 Models Enabled:
True
Performance Trace Logs:
C:\Users\hagherlacher.o\Microsoft\Power BI Desktop Store App\PerformanceTraces.zip
Enabled Preview Features:
PBI_NewWebTableInference
PBI_v3ModelsPreview
Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_ImportTextByExample
PBI_qnaLiveConnect
PBI_inlineExplore
PBI_dataSearchCuration
PBI_azureMapVisual
PBI_dataPointLassoSelect
Disabled DirectQuery Options:
TreatHanaAsRelationalSource
Cloud:
GlobalCloud
DPI Scale:
125%
Supported Services:
Power BI
Formulas:
section Section1;
shared #"ap_supply_chain_sync_insights scsi_global_int_fact" = let
Source = ApacheSpark.Tables("https://eastus2.azuredatabricks.net:443/sql/protocolv1/o/8755884253711570/0814-081746-have977", 2, [BatchSize=null]),
ap_supply_chain_sync_insights_scsi_global_int_fact = Source{[Schema="ap_supply_chain_sync_insights",Item="scsi_global_int_fact"]}[Data]
in
ap_supply_chain_sync_insights_scsi_global_int_fact;
shared #"ap_supply_chain_sync_insights scsi_material_fused_dim" = let
Source = ApacheSpark.Tables("https://eastus2.azuredatabricks.net:443/sql/protocolv1/o/8755884253711570/0814-081746-have977", 2, [BatchSize=null]),
ap_supply_chain_sync_insights_scsi_material_fused_dim = Source{[Schema="ap_supply_chain_sync_insights",Item="scsi_material_fused_dim"]}[Data]
in
ap_supply_chain_sync_insights_scsi_material_fused_dim;
shared #"ap_supply_chain_sync_insights scsi_analysis_tab_int_fact" = let
Source = ApacheSpark.Tables("https://eastus2.azuredatabricks.net:443/sql/protocolv1/o/8755884253711570/0814-081746-have977", 2, [BatchSize=null]),
ap_supply_chain_sync_insights_scsi_analysis_tab_int_fact = Source{[Schema="ap_supply_chain_sync_insights",Item="scsi_analysis_tab_int_fact"]}[Data]
in
ap_supply_chain_sync_insights_scsi_analysis_tab_int_fact;
shared #"ap_supply_chain_sync_insights scsi_gcas_fused_dim" = let
Source = ApacheSpark.Tables("https://eastus2.azuredatabricks.net:443/sql/protocolv1/o/8755884253711570/0814-081746-have977", 2, [BatchSize=null]),
ap_supply_chain_sync_insights_scsi_gcas_fused_dim = Source{[Schema="ap_supply_chain_sync_insights",Item="scsi_gcas_fused_dim"]}[Data]
in
ap_supply_chain_sync_insights_scsi_gcas_fused_dim;
shared #"ap_supply_chain_sync_insights scsi_gcas_weekly_int_fact" = let
Source = ApacheSpark.Tables("https://eastus2.azuredatabricks.net:443/sql/protocolv1/o/8755884253711570/0814-081746-have977", 2, [BatchSize=null]),
ap_supply_chain_sync_insights_scsi_gcas_weekly_int_fact = Source{[Schema="ap_supply_chain_sync_insights",Item="scsi_gcas_weekly_int_fact"]}[Data]
in
ap_supply_chain_sync_insights_scsi_gcas_weekly_int_fact;
shared #"ap_supply_chain_sync_insights scsi_material_snpp_dim" = let
Source = ApacheSpark.Tables("https://eastus2.azuredatabricks.net:443/sql/protocolv1/o/8755884253711570/0814-081746-have977", 2, [BatchSize=null]),
ap_supply_chain_sync_insights_scsi_material_snpp_dim = Source{[Schema="ap_supply_chain_sync_insights",Item="scsi_material_snpp_dim"]}[Data]
in
ap_supply_chain_sync_insights_scsi_material_snpp_dim;
shared BOM = let
Source = Excel.Workbook(File.Contents("C:\Users\hagherlacher.o\Documents\F&F\New All Perfumes.xlsx"), null, true),
BOM_Sheet = Source{[Item="BOM",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(BOM_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"PERFUME-GCAS", Int64.Type}, {"PERFUME-NAME", type text}, {"Perfume/flavor classification", type text}, {"CUSTOMER-PLANT-CODE", type text}, {"CUSTOMER-PLANT-NAME", type text}, {"Perfume price", type number}, {"Perfume spend", type number}, {"CUSTOMER-PLANT-COUNTRY", type text}, {"CUSTOMER-PLANT-REGION", type text}, {"TDCVAL", type text}, {"TDCVAL-NAME", type text}, {"Platform", type text}, {"CATEGORY", type text}, {"SUBSECTOR", type text}, {"SECTOR", type text}, {"PERFUME-PLANT-CODE", type any}, {"PERFUME-PLANT-NAME", type text}, {"PRM-GCAS", Int64.Type}, {"PRM-NAME", type text}, {"PRM Classification", type text}, {"PRM-LEVEL-IN-THIS-PERFUME", type number}, {"VENDOR", type text}, {"Buyer", type any}, {"P&G - P2P Vendor", type text}, {"Incoterms", type any}, {"Incoterms_Desc", type any}, {"Price changed to reflect Worms consignment prices missing", type number}, {"PRM spend incl. Worms consignment prices (and no NA)", type number}, {"PRM spend (no external perfumes)", type number}, {"YEARLY PRM KG VOLUME", type number}, {"Freight", type any}, {"Freight3", type number}, {"Duty", type any}, {"Import Duty", type number}})
in
#"Changed Type";
shared #"BOM (2)" = let
Source = Excel.Workbook(File.Contents("C:\Users\hagherlacher.o\Documents\F&F\New All Perfumes.xlsx"), null, true),
BOM_Sheet = Source{[Item="BOM",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(BOM_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"PERFUME-GCAS", Int64.Type}, {"PERFUME-NAME", type text}, {"Perfume/flavor classification", type text}, {"CUSTOMER-PLANT-CODE", type text}, {"CUSTOMER-PLANT-NAME", type text}, {"Perfume price", type number}, {"Perfume spend", type number}, {"CUSTOMER-PLANT-COUNTRY", type text}, {"CUSTOMER-PLANT-REGION", type text}, {"TDCVAL", type text}, {"TDCVAL-NAME", type text}, {"Platform", type text}, {"CATEGORY", type text}, {"SUBSECTOR", type text}, {"SECTOR", type text}, {"PERFUME-PLANT-CODE", type any}, {"PERFUME-PLANT-NAME", type text}, {"PRM-GCAS", Int64.Type}, {"PRM-NAME", type text}, {"PRM Classification", type text}, {"PRM-LEVEL-IN-THIS-PERFUME", type number}, {"VENDOR", type text}, {"Buyer", type any}, {"P&G - P2P Vendor", type text}, {"Incoterms", type any}, {"Incoterms_Desc", type any}, {"Price changed to reflect Worms consignment prices missing", type number}, {"PRM spend incl. Worms consignment prices (and no NA)", type number}, {"PRM spend (no external perfumes)", type number}, {"YEARLY PRM KG VOLUME", type number}, {"Freight", type any}, {"Freight3", type number}, {"Duty", type any}, {"Import Duty", type number}})
in
#"Changed Type";
