Power Query: Transforming Desktop Queries in Dataflows

.There are some interesting ways to transform queries from Power BI Desktop to Power BI dataflows in the portal.

The UI can be a bit confusing. I made some mistakes in the middle as well. Let’s see some options to make the conversion.

Leaving Blank Query Blank

1. Create a new dataflow
2. Select Blank Query as the source

1. Select the gateway used to access the on premise servers

It seems strange to select a gateway for a blank query. This makes the UI a bit confusing. However, the gateway will be set for the entire dataflow, not only for the blank query

1. On Power BI Desktop, copy the query you would like to convert
2. Use CTRL+V on the keyboard to past the query to the Query Window, inside the dataflow
3. Click the button Configure Connection to define the credential for the query

Since you already selected the gateway, if the server and database name are configured with the same case as in the gateway, Power BI will recover the authentication information from the gateway automatically.

If the case of server and database name is different, it’s better to edit the code first, ensure the case of the server and database name are correct and define the credential later.

If you left the gateway empty, you may notice the button Configure Credential will not recognize the gateway neither leave you an option to choose it.

After having reach the query editor, if you need to change the gateway it needs to be done on the dataflow level. On the menu, Home -> Options -> Project Options allows you to set the gateway

Blank Query for a single Query

If you would like to convert a single query, you don’t need to get into the query editor. This small secret seems so simple, but it’s not so obvious.

Once you select the Blank Query as source, you can past your query from the Power BI desktop in the code area. Power BI will be responsible to understand what you are pasting and will include the code of your query.

You still need to define the gateway on the option for this on the same screen.

Blank Query for Multiple Queries

This is a combination of the previous methods and there are two ways to do it:

1. First method: add a dummy blank query and past the desktop queries on the editor later. You have the option to choose the gateway for the blank query or configure the gateway later, on the project options for the dataflow
2. Second method: create a useful first query, choosing one of your on-premise queries, anyone, and past it on the code area of the first blank query. Then you past the rest on the query area, inside the query editor. You will need to choose the gateway for the first query.

Web Source

The message you receive when migrating one query using web source from the desktop to a dataflow may appear to be different, but it’s very simple: The Web source requires a gateway. You need to have a gateway with the Web source configured so it will work inside a data flow as well.

Functions

When we copy a query using custom M functions, Power BI copy the function as well.

Power BI Desktop organizes the functions in folders and for each function we have one query. We can edit the query visually without touching the function code.

In the dataflow, on the other hand, only the function is copied. Any future edit will need to be made on the function code.

One additional detail: On the desktop, we disable the load of the function query and it works fine. In a dataflow, the function will be imported with Enable Load activated and we probably would like to disable it.

In fact, if we don’t disable it, the query using the functions may become a computed table, making the refresh only available on premium. Once we disable the load of the functions, the table becomes a regular query and the refresh may be allowed.

External Tool

Marcus Wegener (@PowerBIler) created an external tool which exports all queries from a power BI file and allow us to import as a dataflow.

The tool exports the queries as a JSON file. We will be able to use the option Import Model to create a new dataflow on the portal

The external tool is included in the Business Ops tool, which help us to install many very interesting and important external tools in Power BI at once.

There are some differences between the tool and the simple copy and paste, especially in relation to the validation of the query. Anyway, I’m still trying to understand to what scenario each one will be better.

Conclusion

Although everything starts with a Copy/Paste, transferring Power BI queries from desktop to the portal has some tricks and secrets.

 

The post Power Query: Transforming Desktop Queries in Dataflows appeared first on Simple Talk.

from Simple Talk https://ift.tt/2RKeRTM
via