For Power BI enthusiasts delving into data acquisition from web sources, crafting intricate URLs becomes an inevitable task. The Web.Contents() function reigns supreme in this arena, but constructing URLs manually can be cumbersome and susceptible to errors. Fear not, intrepid data navigators!RelativePath and Query are here to save the day, offering a more legant and dynamic approach to URL construction.

The Power of RelativePath and Query

RelativePath: Appends a specified path segment to the base URL within Web.Contents(), ensuring clarity and maintainability. Think of it as adding directions to reach a specific page within a website's directory.

Query: Injects key-value pairs directly into the URL as query parameters, empowering you to filter, sort, or customize data retrieval without altering the core URL structure. Imagine fine-tuning search results on a website by specifying your criteria.

Illustrative Example: Dynamic Weather Data Fetch

Let's embark on a practical journey, fetching hourly weather data for a dynamic city list using RelativePath and Query.

Assumptions:

You have an API that provides weather data for specific cities.

The base URL format is <invalid URL removed>?{parameters}.

You have a list of city names stored in a Power BI table named Cities.

Steps:

Create a Parameter:
- Go to Modeling > Manage Parameters.
- Create a parameter named City with a text data type.

Craft the M Code

let

    Source = Web.Contents(

        "https://api.weather.com/v2/agrometeorological/hourly",

        [

            RelativePath = Text.Combine({"/"}, Parameter.City),

            Query = [

                apiKey = "YOUR_API_KEY", // Replace with your actual API key

                startDate = "2024-02-05", // Adjust as needed

                endDate = "2024-02-06"

            ]

        ]

    ),

    Data = Table.FromList(Source, ShareByValue=true),

    TransformedData = Table.TransformColumns(Data,...) // Your data transformation steps here

in

    TransformedData

Connect to Table
- Select the Cities table.
- Go to Home > Transform Data.
- Paste the M code in the Advanced Editor.
- Replace "YOUR_API_KEY" with your actual API key.
- Click Invoke Custom Function.

SHAREPOINT LIST EXAMPLE

Let's say there are more than 5000 items in your SharePoint list.

You can overcome the issue of "This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources." or "Due to consecutive errors, scheduled refresh has been disabled. Please resolve the error on the dataset and then re-enable.
Data source for Query1"

In the below M Code I've used the select and expand. This way you can Improve the performance.

let
    // Define parameters
    baseurl = "https://{tenantname}.sharepoint.com/sites/{sitename}/_api/web/lists/GetByTitle('listname')/",
    skipCount = 5000,
     fieldselect = "&$top=" & Text.From(skipCount) & "&$select=WeekStartDate,Initiative_x003a_Client,Account_x002d_Initiative,Hours,CreatedBy_x002d_User/Title, CreatedBy_x002d_User/EMail,TaxCatchAll/Term&$expand=CreatedBy_x002d_User,TaxCatchAll", 

    // Get total item count
    itemcount = Json.Document(Web.Contents(baseurl, 
        [
            RelativePath = "ItemCount", 
            Headers=[Accept="application/json"]
        ]))[value],

    // Generate a list of skip values
    skiplist = List.Numbers(0, Number.RoundUp(itemcount/skipCount), skipCount),

    // Create a table from the skip list
    #"Converted to Table" = Table.FromList(skiplist, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Skip"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Skip", type text}}),

    // Get data for each skip value
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Items", each 
    let
        skipValue = [Skip],
        relativePath = "items",
        queryOptions= "?$skipToken=Paged=TRUE%26p_ID=" & Text.From(skipValue) & fieldselect,
        items = Json.Document(Web.Contents(baseurl,
            [
                RelativePath=relativePath & queryOptions,
                Headers=[Accept="application/json"]
            ]
            ))
    in
        items
),
    // Expand the Items column
    #"Expanded Items" = Table.ExpandRecordColumn(#"Added Custom", "Items", {"value"}, {"value"}),

    // Expand the value column
    #"Expanded value" = Table.ExpandListColumn(#"Expanded Items", "value"),
    #"Expanded value1" = Table.ExpandRecordColumn(#"Expanded value", "value", {"TaxCatchAll", "CreatedBy_x002d_User", "WeekStartDate", "Hours", "Initiative_x003a_Client", "Account_x002d_Initiative"}, {"value.TaxCatchAll", "value.CreatedBy_x002d_User", "value.WeekStartDate", "value.Hours", "value.Initiative_x003a_Client", "value.Account_x002d_Initiative"}),
    #"Expanded value.TaxCatchAll" = Table.ExpandListColumn(#"Expanded value1", "value.TaxCatchAll"),
    #"Expanded value.TaxCatchAll1" = Table.ExpandRecordColumn(#"Expanded value.TaxCatchAll", "value.TaxCatchAll", {"Term"}, {"value.TaxCatchAll.Term"}),
    #"Expanded value.CreatedBy_x002d_User1" = Table.ExpandRecordColumn(#"Expanded value.TaxCatchAll1", "value.CreatedBy_x002d_User", {"Title"}, {"value.CreatedBy_x002d_User.Title"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded value.CreatedBy_x002d_User1",{{"value.TaxCatchAll.Term", "Stage"}, {"value.CreatedBy_x002d_User.Title", "Name"}, {"value.WeekStartDate", "WeekStartDate"}, {"value.Hours", "Hours Spent this Week"}, {"value.Initiative_x003a_Client", "Initiative: Client"}, {"value.Account_x002d_Initiative", "Initiative"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"WeekStartDate", type datetime}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"WeekStartDate", type date}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type2",{"Skip", "WeekStartDate", "Initiative: Client", "Initiative", "Stage", "Hours Spent this Week", "Name"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Skip"})
in
    #"Removed Columns"

Here's a breakdown of each step:

Define Parameters:
- baseurl: Specifies the base URL for the SharePoint site and list API.
- skipCount: Defines the number of items to skip in each page.
- fieldselect: Specifies the fields to be retrieved, including some additional parameters like top, select, and expand.
Get Total Item Count:
- Sends a request to the SharePoint API to retrieve the total count of items in the specified list.
Generate a List of Skip Values:
- Creates a list of values to be used as skip parameters in subsequent requests, based on the total item count and the skipCount.
Create a Table from the Skip List:
- Converts the skip list into a table format for further processing.
Transform Skip Column Types:
- Changes the data type of the skip column to text.
Get Data for Each Skip Value:
- For each skip value, sends a request to the SharePoint API to retrieve a page of items, using the $skipToken parameter for pagination.
Expand Items Column:
- Expands the "Items" column, which contains the data retrieved for each skip value.
Expand Value Column:
- Expands the "value" column within the "Items" column, as the actual data is nested within.
Expand Nested Columns:
- Expands specific nested columns like "TaxCatchAll" and "CreatedBy_x002d_User" to access their individual properties.
Rename Columns:
- Renames columns for better readability.
Transform Column Types:
- Changes the data type of the "WeekStartDate" column to datetime and then to date for consistency.
Reorder Columns:
- Reorders the columns for a more organized structure.
Remove Skip Column:
- Removes the "Skip" column, which was used for pagination and is no longer needed in the final output.

The final result is a table with the desired data from the SharePoint list, organized and formatted according to the specified transformations.

All The Tips In One Place