Zoho Creator
Power Query is a robust data management and transformation tool that comes with Microsoft Power BI. It allows you to extract and transform data from various sources and load it into Power BI for analysis and visualization. One of the sources that Power Query can connect to is Zoho Creator, a cloud-based platform for building custom business applications.
In this article, we will explore how to use Power Query M Language code to connect to the Zoho Creator data source from inside Power BI.
Prerequisites
Before we begin, you need to have the following:
– An active Zoho Creator account
– A Zoho Creator application with data that you want to connect to
– Microsoft Power BI Desktop installed on your computer
Step 1: Retrieve the Zoho Creator API Key
To connect to the Zoho Creator data source, you need to retrieve your Zoho Creator API Key. Follow the steps below to do this:
1. Log in to your Zoho Creator account.
2. Click on the “Settings” icon in the top-right corner of the screen.
3. Select “API Key” from the dropdown menu.
4. Click on the “Generate New Key” button.
5. Copy the generated API Key to your clipboard.
Step 2: Create a New Query in Power BI
1. Open Microsoft Power BI Desktop.
2. Click on the “Home” tab in the ribbon.
3. Click on the “Get Data” button.
4. Select “Web” from the list of data sources.
5. In the “Web” dialog box, enter the following URL: https://creator.zoho.com/api/json/{your-username}/{your-application-name}/view/{your-form-name}?authtoken={your-api-key}
6. Replace {your-username}, {your-application-name}, {your-form-name}, and {your-api-key} with your Zoho Creator account details and API Key.
7. Click on the “OK” button to create a new query.
Step 3: Transform the Data
Once you have created the query, you need to transform the data to make it usable in Power BI. Follow the steps below to do this:
1. Click on the “Transform Data” button in the “Query Editor” window.
2. Use the various data transformation tools in Power Query to clean and shape your data.
3. Click on the “Close & Apply” button to save your changes and load the data into Power BI.
Step 4: Refresh the Data
If your Zoho Creator data changes frequently, you may want to set up automatic data refresh in Power BI. To do this, follow the steps below:
1. Click on the “Home” tab in the ribbon.
2. Click on the “Transform Data” button in the “Query Editor” window.
3. Click on the “Options” tab in the ribbon.
4. Under “Current Workbook”, click on “Data Load”.
5. Select “Enable Load” and “Refresh data when opening the file”.
6. Set the desired refresh frequency and click on the “OK” button.
Conclusion
In this article, we have explored how to use Power Query M Language code to connect to the Zoho Creator data source from inside Power BI. By following the steps outlined above, you can easily extract and transform data from Zoho Creator and load it into Power BI for analysis and visualization.
