How to migrate Power BI queries into Bold BI

This article explains the steps involved in migrating native Power BI queries into Bold BI using the code view. Power BI is a Data Visualization and Business Intelligence tool that converts data from different data sources into coherent, visually immersive, and interactive insights. Bold BI supports all the native queries of Power BI application for visualizing data and creating interactive dashboards.

How to get the native query from Power BI?

1. Here we have connected Power BI to the Microsoft SQL server to get the native query. You can connect Power BI to the Microsoft SQL server using the below link.
   https://docs.microsoft.com/en-us/power-bi/fundamentals/desktop-what-is-desktop#connect-to-data
2. After connecting to the Microsoft SQL server, select the tables to be loaded in Power BI.

4. Once the query editor is opened, select the Merge queries as a New option to join the selected tables as a single table.

select [$Outer].[CustomerID] as [CustomerID],
    [$Outer].[CompanyName] as [CompanyName],
    [$Outer].[ContactName] as [ContactName],
    [$Outer].[ContactTitle] as [ContactTitle],
    [$Outer].[Address] as [Address],
    [$Outer].[City] as [City],
    [$Outer].[Region] as [Region],
    [$Outer].[PostalCode] as [PostalCode],
    [$Outer].[Country] as [Country],
    [$Outer].[Phone] as [Phone],
    [$Outer].[Fax] as [Fax],
    [$Inner].[OrderID] as [Orders.OrderID],
    [$Inner].[CustomerID2] as [Orders.CustomerID],
    [$Inner].[EmployeeID] as [Orders.EmployeeID],
    [$Inner].[OrderDate] as [Orders.OrderDate],
    [$Inner].[RequiredDate] as [Orders.RequiredDate],
    [$Inner].[ShippedDate] as [Orders.ShippedDate],
    [$Inner].[ShipVia] as [Orders.ShipVia],
    [$Inner].[Freight] as [Orders.Freight],
    [$Inner].[ShipName] as [Orders.ShipName],
    [$Inner].[ShipAddress] as [Orders.ShipAddress],
    [$Inner].[ShipCity] as [Orders.ShipCity],
    [$Inner].[ShipRegion] as [Orders.ShipRegion],
    [$Inner].[ShipPostalCode] as [Orders.ShipPostalCode],
    [$Inner].[ShipCountry_1] as [Orders.ShipCountry_1]
from [dbo].[Customers] as [$Outer]
left outer join 
(
    select [OrderID] as [OrderID],
        [CustomerID] as [CustomerID2],
        [EmployeeID] as [EmployeeID],
        [OrderDate] as [OrderDate],
        [RequiredDate] as [RequiredDate],
        [ShippedDate] as [ShippedDate],
        [ShipVia] as [ShipVia],
        [Freight] as [Freight],
        [ShipName] as [ShipName],
        [ShipAddress] as [ShipAddress],
        [ShipCity] as [ShipCity],
        [ShipRegion] as [ShipRegion],
        [ShipPostalCode] as [ShipPostalCode],
        [ShipCountry_1] as [ShipCountry_1]
    from [dbo].[Orders] as [$Table]
) as [$Inner] on ([$Outer].[CustomerID] = [$Inner].[CustomerID2])

How to execute Power BI native query in Bold BI?

1. You can connect to the Microsoft SQL server using the Bold BI application with the following link.

2. After connecting to the data source, switch to the code view by enabling the slider option in the tools pane in the data design view, and you can find the query editor window in which you can execute the custom queries as follows.

3. You can enter the native Power BI query in the code view and click on the Run button to execute the query.

4. You can view the data for your query at the bottom of the query editor by clicking Update.

5. Click Save to save the data source with a relevant name to design a dashboard.

6. You can use the following link to create a dashboard using Bold BI, with the fetched data from the Microsoft SQL Server.

You can create multiple data sources to work with the single dashboard in Bold BI.

7. Once the dashboard is created, you can preview the dashboard by clicking the Preview button.

References

• Connect to data in Power BI Desktop
• SQL Data Source in Bold BI
• Working with Dashboards in Bold BI