ISSUE::PBI DESKTOP::QUERY FOLDING TO LAKEHOUSE NOT WORKING

[Element115]

CONTEXT:

I have this table schema in a lakehouse:

PBI Desktop Version: 2.128.952.0 64-bit (April 2024)

ISSUE:

0__I am trying to group the Refresh dates in 15 min bins:

like so:

1__the visual I use is a matrix and looks like this before dropping the new field:

the data well looks like this:

2__after creating the group and dropping the new field into the matrix data well:

I get this error:

The matrix should look like this:

QUESTION:

Does this mean that the only way of achieving this binning/grouping would be to do it in Fabric with a DFg2 in order to avoid the query folding?  This could be an issue as currently I am using DB mirroring in a pipeline to ingest the data incrementally.  Not keen to switch it over to a DFg2 as it seems to be less efficient. But perhaps I am mistaken on the latter part. 

[amitchandak]

@Element115 , You should create a Bin in Lakehouse using Spark or DataflowGen2 (Using if else).  And should ensure a Direct lake connection.  This seems like using direct query and can have issues like that

[Element115]

Your assessment is incorrect. Apparently it is a transient bug and went away on its own as there are no error messages when I try binning a field from a LH table in DirectQuery mode.

[Element115]

@amitchandak Do you know whether query folding would work if I used a warehouse instead of a lakehouse?

[Element115]

Thanks for the quick response, but I can't use a DirectLake connection as this is a report developped and maintained with PBI Desktop, which supports data access only in Import, DirectQuery, or Dual mode. 

The issue I have is that the source has a binary column that needs to be transformed to  type text, which can be done in a DFg2 no problem.  But the thing is, I do all the initial ingest using a Copy data activity in a pipeline, and when you do this, the binary column is persisted in the LH as a binary column, same as the source.  

So if I were to later use a DFg2 to transform this colum to text, I will duplicate my data, once in the LH, and a second time in the default Fabric store because I would have enabled staging.

The only solution I see at this point is to not use the new mirroring capability of the pipeline artifact and instead ingest everything only through a DFg2 which would write to the LH the binary column as a text type, while at the same time binning the data.  This way no data duplication occurs.  

This means I would lose the new capability of the pipeline bulk copy and consume more CUs because of the additional DFg2.

Not optimal all this.

[Element115]

Or just create a view at the source and base64 encode that varbinary column to varchar(MAX).  Problem solved.  Although it still irks that you can't fold queries to a LH, or for that matter, that a LH can't handle type time.