Category: Power BI

Get API data with Power Query (M)

This is a super useful function I use all the time, which takes a url as a string and makes a get request, then deserialises the data returned.

let
    getJsonFromUrl = (url as text) =>
        let
            response = Web.Contents(url),
            json = Json.Document(response)
        in
            json
in
    getJsonFromUrl

In this example, we define a custom function called getJsonFromUrl that takes a single parameter called url of type text. We then use the Web.Contents function to make an HTTP GET request to the url parameter, which returns the response as a binary data stream. We then use the Json.Document function to deserialize the response into a structured JSON object.

To use this custom function, simply invoke it with the desired URL as a parameter. For example, to get the JSON data from “https://example.com/api/data“, you would use the following formula in Power Query:

getJsonFromUrl("https://example.com/api/data")

If you need to add authorization headers:

let
    getJsonFromUrlWithAuth = (url as text, token as text) =>
        let
            headers = [#"Authorization"= "Bearer " & token],
            response = Web.Contents(url, [Headers=headers]),
            json = Json.Document(response)
        in
            json
in
    getJsonFromUrlWithAuth

Custom Functions in Power Query M That Take Parameters

Power Query is a powerful data processing tool that is built into Microsoft Excel and is also available as a standalone product. One of the most useful features of Power Query is the ability to create custom functions in M, the programming language that powers Power Query.

In this article, we will explore how to create a custom function in Power Query M that takes parameters.

What is Power Query M?

Power Query M is a functional programming language used to manipulate and transform data in Power Query. M is used to write formulas, queries, and custom functions in Power Query.

What are Custom Functions in Power Query M?

Custom functions in Power Query M are user-defined functions that can be used to transform data in Power Query. These functions can be reused across multiple queries and workbooks, saving time and effort.

Custom functions in Power Query M can take one or more parameters. Parameters are input values that are passed to the function when it is called.

How to create a Custom Function with Parameters

Creating a custom function in Power Query M that takes parameters is a straightforward process. Here is a step-by-step guide to creating a custom function with parameters:

Step 1: Open the Power Query Editor

The first step is to open the Power Query Editor. This can be done by selecting “Data” from the Excel ribbon, and then selecting “From Table/Range” or “From Other Sources” depending on where the data is located.

Step 2: Create a Blank Query

Once the Power Query Editor is open, create a new blank query by selecting “New Source” and then “Blank Query” from the “Home” tab.

Step 3: Open the Advanced Editor

In the new blank query, select “View” and then “Advanced Editor” from the ribbon. This will open the Advanced Editor window, where you can write your custom function.

Step 4: Define the Function

To create a custom function with parameters, start by defining the function. Here is an example of a simple custom function that takes a text location parameter and uses the free nominatim API:
(it even delays the call for 1 second to keep with their rate limits)

let geocode_location = (location as text) => 
        let json_response = Function.InvokeAfter( () => Json.Document(Web.Contents("https://nominatim.openstreetmap.org/search.php?q="""& location &"""&format=jsonv2")),#duration(0,0,0,1))
in geocode_location

Power BI, Microsoft’s popular business intelligence platform, has received several new updates in March 2023

These updates include enhancements to data modeling, visualization, and collaboration features. These updates include enhancements to data modeling, visualization, and collaboration features. In this article, we’ll take a closer look at the key features of the March 2023 Power BI updates, along with some examples of how these updates can be used.

  1. Improved data modeling capabilities:

One of the most significant updates in March 2023 is the improved data modeling capabilities in Power BI. With the ability to create and use composite models, users can combine data from multiple sources into a single data model. For example, if you have sales data in one table and customer data in another, you can combine these tables to create a single data model that shows sales by customer.

Another new feature is the support for bidirectional cross-filtering, which enables users to filter data in both directions between tables in a data model. For example, you can use bidirectional cross-filtering to show sales data for a specific customer and also show the customer’s purchase history.

  1. Enhanced visualization features:

Power BI’s visualization capabilities have also been improved with several new features. For example, you can now create custom visuals using the Power BI SDK. This allows developers to create and share custom visualizations that can be used within Power BI reports.

Another new visualization feature is the ability to create drill-through reports. This allows users to navigate to more detailed reports based on a specific data point. For example, if you have a report that shows sales by region, you can create a drill-through report that shows sales by city for a specific region.

  1. Improved collaboration features:

Power BI’s collaboration features have also been enhanced with several new capabilities. For example, you can now create and share templates, which allow users to create standardized report layouts that can be used across different teams and departments.

Another new feature is the ability to annotate and highlight data points within reports. This makes it easier for users to collaborate and share insights. For example, if you have a report that shows sales data, you can annotate a specific data point to highlight a trend or pattern.

Overall, the new March 2023 Power BI updates offer several enhancements to the platform’s data modeling, visualization, and collaboration features. These updates should make it easier for users to work with data and create insightful reports that can be shared with their teams and stakeholders.

If you want to learn more about the March 2023 Power BI updates, check out the official Microsoft blog post here: [https://powerbi.microsoft.com/en-us/blog/power-bi-march-2023-feature-summary/].

You can also find more information about Power BI on the official Power BI website: [https://powerbi.microsoft.com/].