Spigit
Power Query is a powerful data transformation and data connection tool in Power BI that can be used to connect to various data sources. One such data source is Spigit, which is an innovation management software that allows organizations to crowdsource ideas from their employees and customers. In this article, we will explore how to use Power Query’s M language code to connect to the Spigit data source from inside Power BI.
Prerequisites
Before we begin, you should ensure that you have the following prerequisites in place:
– A Power BI account
– A Spigit account
– Access to the Spigit API
Getting Started
To connect to the Spigit data source, we will first need to get the API key. Follow the steps below to generate an API key in Spigit:
1. Log in to your Spigit account.
2. Go to Settings > API Keys.
3. Click on the “Generate New Key” button.
4. Enter a name for the API key and select the permissions.
5. Click on the “Generate” button to generate the API key.
Once you have generated the API key, copy it as we will need to use it later.
Connecting to Spigit from Power BI
Now that we have the API key, we can use Power Query to connect to the Spigit data source. Follow the steps below to connect to Spigit:
1. Open Power BI Desktop and click on “Get Data” in the Home tab.
2. In the “Get Data” dialog box, select “Web” and click on “Connect.”
3. In the “From Web” dialog box, enter the Spigit API endpoint URL in the URL field. For example, if your Spigit domain is “mycompany.Spigit.com,” the API endpoint URL would be “https://mycompany.Spigit.com/api/v2/ideas.”
4. Click on OK and you will see the Navigator dialog box open up.
In the Navigator dialog box, you will see the data from the Spigit API in a table format. You can select the data that you need and click on “Load” to load it into Power BI.
Using M Language Code to Connect to Spigit
The above steps are sufficient for connecting to Spigit from Power BI. However, sometimes we need to customize the data that we get from the API. This is where M language code comes into play.
M language is the formula language used in Power Query. It is a functional language that allows us to transform and shape data. In our case, we can use M language code to customize the data that we get from the Spigit API.
Let’s say that we want to get only the ideas that were submitted in the last month. We can use the M language code to filter the data. Follow the steps below to filter the data:
1. Open Power BI Desktop and click on “Get Data” in the Home tab.
2. In the “Get Data” dialog box, select “Web” and click on “Connect.”
3. In the “From Web” dialog box, enter the Spigit API endpoint URL in the URL field. For example, if your Spigit domain is “mycompany.Spigit.com,” the API endpoint URL would be “https://mycompany.Spigit.com/api/v2/ideas.”
4. Click on “OK” and you will see the Navigator dialog box open up.
In the Navigator dialog box, click on “Edit” to open the Power Query Editor. Here, we will use the M language code to filter the data. Follow the steps below to do so:
1. Click on the “Transform Data” button in the Home tab.
2. In the Power Query Editor, click on “Add Column” in the “Add Column” tab.
3. Enter the following M language code in the formula bar:
`= Json.Document(Web.Contents(“https://mycompany.Spigit.com/api/v2/ideas?$filter=created_on ge ‘2022-01-01’ and created_on le ‘2022-01-31′”, [Headers=[#”Authorization”=”Bearer
Replace “mycompany.Spigit.com” with your Spigit domain, and replace “
The above M language code will filter the data and get only the ideas that were created between January 1, 2022, and January 31, 2022. You can modify the code to filter the data as per your requirements.
4. Click on “OK” to close the formula bar.
Now, you will see the data with the filtered results. You can click on “Close & Apply” to load the data into Power BI.
Conclusion
Power Query is a powerful data transformation and data connection tool in Power BI that can be used to connect to various data sources. In this article, we explored how to use Power Query’s M language code to connect to the Spigit data source from inside Power BI. We also saw how to use M language code to filter the data as per our requirements. By using M language code, we can customize the data that we get from the API and use it to create powerful visualizations in Power BI.
