Forum Discussion
refresh challenge for directquery and calculated table
Hi,
Seems the issue is that your additional sellers from Excel don’t have details like city. This makes it impossible to combine them with the DirectQuery table in a calculated table and maintain proper relationships for visuals.
I see 3 possible options:SQL view in the source:
If possible, create a view that UNIONs all sellers and includes all necessary details (city, etc.)...This gives you a single, consistent table for slicers and visuals.
Mapping table for additional sellers
Keep DirectQuery as master...then add a small Excel table with the extra sellers and their details (city, etc.).
Use relationships + DAX measures to handle the extra sellers in your visuals
Separate slicer table
You could create a slicer table with all sellers, but visuals using city/details need measures to handle missing info for Excel only sellers..
Hope that helps !! Regards.
wrt Option 1: I do not have access within Power Query (at least I'm not able to manage this right now) to the DirectQuery table which is looped in via our OneLake data hub. I could manage reference tables, appand... for the other two tables which are basically excel downloads - replaced/renewed regulary. As I'm doing this by my own, I have full control. I'm therefore not sure with the proposals to have the DIM_seller created in there or if I would need an additional 'dummy' table/file to create this. I'm not an expert at all, is there an easy way to have this done???
Thank you so for all the ideas provided so far.
Kind regards Stefan
Responsive ResidentHi, I See...
Try with these Steps:
Create references for the two Excel tables in Power Query.
Right-click the table → “Reference.” This creates a copy you can safely transform.
Keep only the seller column in each reference.
You can remove other columns by selecting them → “Remove Columns.”
Append the tables.
Append Queries → select the two references. This will combine the seller lists into one table.
Remove duplicates.
Select the seller column → Remove Duplicates. Now you have a unique list of sellers.
Load this as a new table in your model.
This table can act as your slicer/dimension table.
If you want, you could also create a small “dummy” Excel file that just lists the sellers, then refresh it whenever the Excel downloads are updated. This might make it easier to manage and keeps everything under your control.
This approach avoids the DirectQuery issue because the calculated table isn’t needed anymore.
Let me know. Hope it helps!
Regards,
Ricardo
Good morning,
thank you for the additional hints.
Steps 1 -5 are clear and this is working. However I need to have the sellers from the DirectQuery as well. In addition, there are additional information within the DirectQuery list which I'm trying to show in a report table. This isn't wokring either despite several approaches with the relationship settings e.g. many-to-may and filter in both directions between the tables directly or between the calculated table. Let me try to illustrate to issue...
The DirectQuery table is like a master table, with the challange that not all sellers are listed... Therefore all sellers form the master AND all additional sellers from the other tables shall be within the calculted table to enable one filter/slicer to generate a report with a visual and table where all sellers are listed together with additonal details, like city, and other calculations...
Thank you in advance. Cheers Stefan
DirectQuery calc. table seller city seller city Liam London Liam London Stuart Paris Stuart Paris Stefan 😊 Berlin --> Stefan Berlin Excel1 Jules ""(empty) Jules Nicola ""(empty) Stuart Excel2 Nicola - RicardoTraNa
Hi,
Seems the issue is that your additional sellers from Excel don’t have details like city. This makes it impossible to combine them with the DirectQuery table in a calculated table and maintain proper relationships for visuals.
I see 3 possible options:SQL view in the source:
If possible, create a view that UNIONs all sellers and includes all necessary details (city, etc.)...This gives you a single, consistent table for slicers and visuals.
Mapping table for additional sellers
Keep DirectQuery as master...then add a small Excel table with the extra sellers and their details (city, etc.).
Use relationships + DAX measures to handle the extra sellers in your visuals
Separate slicer table
You could create a slicer table with all sellers, but visuals using city/details need measures to handle missing info for Excel only sellers..
Hope that helps !! Regards.