Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
timokrnmr
Frequent Visitor

COMBINEVALUES in Databricks/Spark Direct Query cannot be folded

Hi,

im trying to create a calculated Column which combines two columns to create a composite key, so i can use relations in my model.
I am using Apache Spark direct query mode to connect to Databricks data.
I have found some previous posts about this issue but they were all pretty old and none of the recommended Workarounds worked for me.

My Current Attempt is this:

CompositeKey = COMBINEVALUES("-",'iotstreaming_braketest'[uniqueid], 'iotstreaming_braketest'[Timestamp])
Previously also tried to approach this using the CONCATENATE function or the "&" operator, all resulting in the same error.

With my attempts i always get the "OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression.." Error message.

I assume Timestamp being a Datetime column could cause some issues.

If there are any solutions to fix my attempts or a completely different approach please let me know.
I dont want to resort to creating the column in my databricks source.

Some sample Data:
uniqueid has the Data type "Text" 
Timestamp has the Data type "Date/time"
uniqueid,Timestamp
H912-321RB-200-KF233.M015SZ31SO414,24.01.2023 00:06:28
H984-185RB-100-KF233.M013SZ20PD311,24.01.2023 00:06:35
H905-036RB-100-KF150.M055SZ20TFCH1,24.01.2023 00:06:48
H923-020RB-100-KF150.M854SZ20THDH1,24.01.2023 00:06:49


2 REPLIES 2
SamInogic
Super User
Super User

Hi,

As per our understanding, you want a Calculated Column in Power BI which include combined value from Unique ID and Timestamp column.

We can see in your Given Data the Date/Time is in 24.01.2023 00:06:28

which contains the " . " instead of the /  but the Date Time Format in Power BI do not include any of the format using “.”

 

So, you need to first convert the current data into correct format by creating a new Calculated column for the Timestamp with Following Dax Expression:

 

New TimeStamps = SUBSTITUTE('Sample Table'[TIme Stamp],".","/")

Then Create the Desired Column CompositeKey Using the Below Dax Expression,

CompositeKey = COMBINEVALUES("-",'Sample Table'[Unique ID],FORMAT('Sample

Table'[New TimeStamps],"m/d/yy hh:nn:ss"))


This will result in combined values as shown in the below screenshot,

SamInogic_0-1675155046996.jpeg

 

If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.


Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

Inogic Professional Services: Power Platform/Dynamics 365 CRM
An expert technical extension for your techno-functional business needs
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/

Hi,

Thanks for your answer, however the SUBSTITUTE Function is not available in direct query mode:

timokrnmr_0-1675157892213.png

 

I dont believe this was the issue anyways, i think the "." instead of "/" is simply displayed this way because its the German standard, the column datetype is Date/Time in the model Tab:

timokrnmr_1-1675157892137.png

 

I know its not easily possible to test with direct query mode but my issue is not reproducable in import mode.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors