Unable to combine data based on privacy levels, dataflow workaround does not work

Hi,

I am having the same error as many people to do with the limitation in power bi service about specifying privacy levels: https://learn.microsoft.com/en-us/power-bi/enterprise/desktop-privacy-levels#:~:text=Ignore%20the%20Privacy%20Levels%20and%20potentially%20improve%20performance%20doesn%27t%20work%20in%20the%20Power%20BI%20service.%20Reports%20created%20in%20Power%20BI%20Desktop%20with%20this%20setting%20and%20published%20to%20the%20Power%20BI%20service%20don%27t%20adhere%20to%20the%20setting.%20However%2C%20the%20privacy%20levels%20are%20available%20on%20the%20personal%20gateway.

My understanding is that power bi service instead automatically decides privacy levels based on credentials, however all of my sources use Oauth2 organisational privacy and I am still unable to combine. My ideal solution would be to fix this and have it working this way.

I see that people have suggested using dataflows as a workaround to this limitation instead as you can enable 'ignore privacy levels' this way: Solved: Unable to combine data - Microsoft Fabric Community

When trying this workaround I get the error below for my dataset that connects to the dataflow.

I have attempted this with two different dataflows. I have also configured credentials for the dataset connecting to the dataflow.

Everything works in power bi desktop, I just cannot refresh either way in power bi service.

Details of how I am combining data is as below:

* import excel spreadsheet with column A

* custom column = sql.database(xxx, xxx, [Query="...."&[A]&"..."] where the query is a string that incorporates the field A

* then Table.Combine(customcolumn) to expand. The query always returns a table with the same schema.

What do I need to do to be able to refresh my dataset (either with or without dataflows) in power bi service.