SweetIQ
If you are working with Power BI, then you are likely already aware of the incredible benefits it offers. One of the best things about this powerful data analytics tool is that it allows you to connect to a wide range of data sources, including SweetIQ.
SweetIQ is a local marketing automation platform that helps businesses optimize their online presence. Being able to connect to this data source from within Power BI can provide valuable insights into your online marketing efforts. In this article, we will explore how you can use Power Query M language code to connect to the SweetIQ data source from inside Power BI.
Understanding Power Query M Language
Before we dive into the specifics of connecting to SweetIQ, it is important to have a basic understanding of Power Query M language. This language is used to manipulate data within Power Query, which is a data transformation and cleaning tool that is built into Power BI.
Power Query M language is an easy-to-learn language that offers powerful data transformation capabilities. Using M language, you can perform a wide range of data manipulation operations, such as filtering, sorting, grouping, and more. Additionally, M language makes it easy to connect to a wide range of data sources, including SweetIQ.
Setting up a SweetIQ Data Source in Power BI
Before you can connect to SweetIQ from Power BI, you will need to set up a data source connection. To do this, you will need to have a SweetIQ account and access to the API key.
To set up the SweetIQ data source in Power BI, follow these steps:
1. Open Power BI Desktop and click on the ‘Get Data’ button on the Home tab.
2. In the ‘Get Data’ window that appears, select ‘Web’ as the data source.
3. In the ‘From Web’ window that appears, enter the SweetIQ API URL and click ‘OK’.
4. Enter the API key in the ‘Web API’ window that appears and click ‘Connect’.
Once you have set up the SweetIQ data source, you can use Power Query M language code to connect to it and manipulate the data.
Using Power Query M Language for SweetIQ Data Manipulation
To connect to the SweetIQ data source using Power Query M language code, follow these steps:
1. Open Power BI Desktop and click on the ‘Transform Data’ button on the Home tab.
2. In the Power Query Editor window that appears, click on the ‘New Source’ button in the ‘Home’ tab.
3. Select ‘Web’ as the source type and enter the SweetIQ API URL.
4. In the ‘Web API’ window that appears, enter the API key and click ‘OK’.
5. Once the data source has been added, click on the ‘Transform Data’ button to open the Power Query Editor.
6. In the Power Query Editor, you can use M language code to manipulate the SweetIQ data. For example, you can filter the data based on specific criteria, group the data by certain attributes, or rename columns.
Here is an example of M language code that filters the SweetIQ data to show only the data for a specific location:
let
Source = Json.Document(Web.Contents(“https://api.SweetIQ.com/v3/locations”)),
locations = Source[locations],
#”Converted to Table” = Table.FromList(locations, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Expanded Column1″ = Table.ExpandRecordColumn(#”Converted to Table”, “Column1”, {“id”, “name”, “address1”, “address2”, “city”, “province”, “postal_code”, “phone”, “email”, “website”, “lat”, “lng”, “status”, “created_at”, “updated_at”, “client_id”, “primary_category_id”, “timezone”, “has_review_monitoring”, “has_social_media_monitoring”, “has_listing_sync”, “has_report_builder”, “has_google_my_business_sync”, “has_google_analytics_sync”, “has_google_ads_sync”, “has_google_maps_sync”}, {“id”, “name”, “address1”, “address2”, “city”, “province”, “postal_code”, “phone”, “email”, “website”, “lat”, “lng”, “status”, “created_at”, “updated_at”, “client_id”, “primary_category_id”, “timezone”, “has_review_monitoring”, “has_social_media_monitoring”, “has_listing_sync”, “has_report_builder”, “has_google_my_business_sync”, “has_google_analytics_sync”, “has_google_ads_sync”, “has_google_maps_sync”}),
#”Filtered Rows” = Table.SelectRows(#”Expanded Column1″, each ([name] = “Location Name”))
in
#”Filtered Rows”
This code filters the SweetIQ data to show only the data for a location with the name ‘Location Name’. Of course, you can modify this code to filter the data based on any other criteria that you require.
Conclusion
Connecting to the SweetIQ data source from inside Power BI can provide valuable insights into your online marketing efforts. By using Power Query M language code, you can manipulate the data from SweetIQ in powerful ways and gain even more insights. With the steps outlined in this article, you can get started with connecting to SweetIQ and using M language for data manipulation in Power BI.
