Switching from imported data to DirectQuery or Live Connection in Power BI

You can switch from Import to DirectQuery or Live Connection, and from DirectQuery to Live Connection

In some cases, it may be necessary to separate a Power BI report from its dataset. For example, you may want to have multiple reports based on a single dataset, or you may have upgraded to Analysis Services. Currently, Power BI Desktop does not provide a simple way of switching from imported data to Live Connection or DirectQuery, but there are some workarounds.

Switching to Live Connection

Several people have already blogged about changing the connectivity mode from Import to Live Connection:

  1. Erik Svensen described how you can switch from imported data to Power BI service or Azure Analysis Services by exploiting the .zip nature of .pbix files.
  2. Kasper de Jonge shared a way to re-bind any Power BI report to SSAS (or any Power BI dataset) by using Power BI REST API.
  3. Rui Romano has shown the simplest way of converting a Power BI Desktop file from Import to Live Connection. I’ve heard Peter Myers sharing the same technique last year as well.

All these techniques rely on the following rule:

Every field you use anywhere in your report must exist with the same name in the same place in your new dataset.

For example, if you have a .pbix file with imported data, and you are using the Sales Amount measure from the Sales table in one of your visuals, then a measure with the same name must exist in the Sales table in the new dataset. If your new dataset contains a column called Sales Amount, then the visual that used the measure will display an error.

On the other hand, if you have more fields in your new dataset, that’s okay. Also, if your new dataset does not have some of the fields from the old dataset, but they are not used anywhere in the report, that’s fine too. This is because Power BI visuals internally rely on field names and not the datasets being identical.

You can also switch from DirectQuery to Live Connection by using one of the techniques above.

Switching to DirectQuery

Here’s something I discovered myself: by using one of the techniques above, you can go from imported data to DirectQuery! I still hear some people saying that it is not possible to switch from imported data to DirectQuery, so I decided to set the record straight.

For instance, you can delete all your queries by following the steps in Rui’s blog post, and then connect to your database and choose DirectQuery instead of Import. The tricky part is getting all the tables and columns with the exact same names of course.

Therefore, if you perform complex transformations in your queries, it may be a good idea to save all queries in a text file before deleting them. You can follow these steps to do so:

  1. Open Power Query Editor by clicking Edit Queries.
  2. Make sure the top query is selected.
  3. Hold the Shift key and click on the bottom query — this will select all queries.
  4. Press Ctrl + C.
  5. Open your favorite text editor (like Notepad).
  6. Press Ctrl + V.

You will then see something like this:

All M queries saved to text

Note how every query is preceded by its name.

Once you delete all your queries, you will need to have as many queries as you originally had (assuming you used fields from all of them). Unfortunately, you will not be able to use the Blank Query source (if you try that, you will be prompted to switch to Import mode). However, you can still make use of your text file with queries:

  1. Connect to any view or table from your data source by using DirectQuery and click Edit instead of Load.
  2. Duplicate your query as many times as you need.
  3. Rename all queries to the correct names. You can get the exact names from the text file you created before.
  4. In each query, open Advanced Editor and replace the code by copying the relevant query from the text file.

Even with this technique, all the limitations of DirectQuery still remain in place. You will probably need to do some extra modeling (create measures and relationships, etc.), but depending on your situation, it may involve less effort compared to rebuilding your reports completely.

  • Barbara Raney

    Great tips! To easily get all of the Power Queries in any data model, select File > Help > About. Then select “Copy Session Diagnostics to Clipboard”. Open Notepad (or your favorite editor) and paste. You will get not only cool information about all of your settings, but search for the phrase “section Section1;” and all of your queries will be listed!

  • Himadrish

    Awesome tips! Thank you for sharing!

  • gu1le

    Hello, I looked this up after most of the presenters at the world tour today said it couldn’t be done. I’m keen to move from an import to a mixed model, as not all the tables come from the same database – but the big ones do and it would be great to have them as direct query. This must be a common situation? Awesome preso today.

    • Thank you, Guy 🙂 Once composite models become generally available, I’m sure the situation you describe will be quite common! Just keep in mind that the feature is currently in preview, and these models can’t be published to Power BI service.

  • anil kumar gutlapalli

    What is the benefit of doing this when the limitations of DirectQuery still apply?

    • In some cases, DirectQuery is pretty much the only option — when you need near-real-time data, for instance.

  • SR


    I want to change power Bi Report file datasource connection import mode to Direct mode via Programmatically/ automated.
    Right now i am doing this process manually, So can we make this process automated through any scripts.??

  • Dennis Vincent

    Do not attempt to convert your pbix from desktop to server version of BPI, prior to making the change. I tried after converting and had trouble, went back to the original file and dropped queries then made the switch w/o any trouble.
    Then converted.

  • mark goldin

    Is there other way around? Going from DQ to Import?