Avoid direct query by dumping data to CSV?

[Anonymous]

I need to use Direct Query to connect to a data model and the data is not cleaned and needs to be manipulated greatly. I keep running into the error in Power Query, "This step results in a query that is not supported in DirectQuery mode." The communication pipeline with the data architect is making it very hard to get anything done.

To avoid Direct Query, could I build a Python script to dump the data on my Remote Desktop as a CSV or JSON file every 5 minutes and then use Import mode inside Power Bi, would that work?

My python script would grab all of the data on the initial load and then every 5 minutes would check for new rows in the Database. 

[parry2k · Accepted solution]

@Anonymous you cannot do DQ to CSV, only Import. How big is a dataset that needs to be DQ? There are many factors when it comes to these kinds of modeling discussion. If it needs to be DQ then most of the time you want data to be prepared/cleaned/transformed at source since there is a limited transformation you can do in PQ. Again this is not one size fits all, a lot of items go into the discussion when you go between DQ/Import and Mixed mode.

[parry2k]

@Anonymous yes that would work, your data source for Power BI will be CSV file instead of your backend system. Once you have CSV files in import mode, you can make changes as you see fit.

But it leads to a question of why you cannot use Import instead of DQ rather having intermediate steps in between using Python etc.

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

I guess I do not fully understand the data pipeline, let me explain more.  They need the data to be refreshed as much as possible and they do not pay for Live data so I believe this is why they choose to go with DirectQuery.  Should the data architect worry about cleaning this data before we query it?  Or is my solution possible with a python script and possibly connecting DirectQuery to the CSV files after it has been cleaned in Python?