Entersoft Business Suite
Power BI is a popular tool for creating data visualizations and making sense of large amounts of data. One of the keys to getting the most out of Power BI is being able to connect to different data sources. In this article, we will explore how to connect to the Entersoft Business Suite data source from inside Power BI using the Power Query M language.
What is Entersoft Business Suite?
Entersoft Business Suite is a business management software designed to help organizations manage their operations more efficiently. It includes modules for finance, sales, inventory management, and customer relationship management. With Entersoft Business Suite, organizations can streamline their workflows, reduce manual tasks, and improve decision-making.
Why Connect to Entersoft Business Suite from Power BI?
Power BI is a powerful tool for data analysis and visualization, but it’s only as good as the data it can access. By connecting to Entersoft Business Suite from Power BI, organizations can create reports and dashboards that provide real-time insights into their operations. This can help organizations make better decisions, identify trends, and uncover opportunities for improvement.
Using Power Query M Language to Connect to Entersoft Business Suite
Power Query is a data transformation and query language that is built into Power BI. With Power Query, you can connect to a variety of data sources, transform and clean the data, and load it into Power BI for analysis and visualization.
To connect to Entersoft Business Suite from Power BI, we will use the OData connector in Power Query. OData is a protocol for creating and consuming RESTful APIs, and Entersoft Business Suite provides an OData API that we can use to connect to the data source.
Here’s how to connect to Entersoft Business Suite from Power BI using Power Query:
1. Open Power BI Desktop and click on “Get Data” in the Home tab.
2. In the “Get Data” window, select “OData” from the list of connectors.
3. In the “From OData Feed” window, enter the URL for the Entersoft Business Suite OData API. This will typically be in the format http://your-server-name:port/EntersoftOData/api/v1/. Click OK.
4. In the “Navigator” window, select the tables and columns you want to load into Power BI. You can preview the data by clicking on the arrows next to each table.
5. Click “Load” to load the data into Power BI.
Writing the M Language Code
When you click “Load” in the previous step, Power Query will generate M language code to connect to the Entersoft Business Suite data source and load the data into Power BI. This code can be modified and customized to meet your specific needs.
Here’s an example of the M language code that Power Query generates to connect to Entersoft Business Suite:
let
Source = OData.Feed(“http://your-server-name:port/EntersoftOData/api/v1/”),
dbo_Customer = Source{[Name=”dbo_Customer”,Kind=”Entity”]}[Data],
#”Filtered Rows” = Table.SelectRows(dbo_Customer, each ([CustomerID] <> null)),
#”Expanded SalesOrderHeaders” = Table.ExpandTableColumn(#”Filtered Rows”, “SalesOrderHeaders”, {“SalesOrderNumber”, “TotalDue”}, {“SalesOrderHeaders.SalesOrderNumber”, “SalesOrderHeaders.TotalDue”})
in
#”Expanded SalesOrderHeaders”
This code connects to the Entersoft Business Suite OData API and retrieves data from the “dbo_Customer” table. It then filters the data to remove any rows where the “CustomerID” column is null, and expands the “SalesOrderHeaders” column to include the “SalesOrderNumber” and “TotalDue” columns.
You can modify this code to include additional tables and columns, apply transformations to the data, and more. The possibilities are endless with M language and Power Query.
Conclusion
In this article, we explored how to connect to the Entersoft Business Suite data source from inside Power BI using the Power Query M language. By connecting to Entersoft Business Suite, organizations can create powerful data visualizations and gain insights into their operations. With Power Query, you can easily connect to a variety of data sources and customize the M language code to meet your specific needs.
