Loading Dynamic Web Data in Power BI

Power BI Desktop allows users to connect the data from a wide range of sources, including Excel, SQL Server, Snowflake, SharePoint folders, Azure blobs, Web URLs, etc.

The process of dynamically loading web URL data will be covered in this blog post. To illustrate this, Let’s take Holiday Analysis data as an example. Retrieving the information for the holiday analysis can be done by visiting the following URL: https://www.timeanddate.com/holidays/india/

STEP 1:
Open Power BI Desktop and create a new PBIX file.

STEP 2:

• Click the “Transform Data” option under the Home ribbon and select “Manage Parameters”.
• A new pop-up window will open after selecting the “New Parameter” option. There is a requirement to supply inputs regarding the parameter.
• Just give the parameter a name that makes sense. One of the best practices in Power BI is to adhere to professional naming conventions.

• Now Select ‘Type’ as text. Under suggested values, there are few options like
  1. any value
  2. list of values
  3. Query
• Click on the “Any value” option to enter the “Current value” as necessary. This option allows to choose any year. If it is necessary to limit inputs to a specific year range, choose “List of values” and enter the list of years that meet the requirement.

STEP 3:

Click the ‘Get data’ dropdown which was also under the ‘Home’ ribbon and select ‘Web’ as a Data Source.

STEP 4:

• A pop-up window will now appear. Paste the online source’s URL into it, then click “Advanced” at the top of the pop-up window.
• The field for advanced options is now active. There is an input area under the given URL. This option designates the subsequent URL portion, in this example, it’s nothing but the Year. Choose the previously created parameter by clicking on “Parameter” in the list of available data types for this particular input field.

STEP 5:

• After this process now Power BI shows all the tables in the provided URL. To determine which table best fits the requirement, preview each one.

• The example table includes a few undesired items, such as null values. To prevent that, the data must be modified using the “Transform Data” option to make it as suitable as possible for the requirement.

STEP 6:

• Even though the generated parameter’s current value is for the year 2020, the transformed data returns information for the current year. While verifying the loaded data with the actual one, except that year part in the Date column, all the values are from the year 2020 as per the input.
• In order to fix this issue, the Date-Month (“dd-mm”) component of the Date column must be combined with the year value, that was just extracted from the newly generated parameter.
• To do this, click the Date column and then click the “Add columns” ribbon. There is an option here named “Extract”.
• This allows to create Date-Month and Year as separate columns.

STEP 7:

• Simply select the “Custom Column” icon under the “Add Columns” ribbon to retrieve the entered year value from the generated parameter. Enter the name of the constructed parameter inside the curly brackets as shown in the below-provided example inside the ‘Custom Column formula’ part of the opened pop-up.

• Click ‘OK’. Then expand the new column by clicking the expand icon in the column header.

STEP 8:

• The Date-Month and Year are now displayed as separate columns, to combine them, simply control-click both columns and choose the “merge columns” option from the “Transform” ribbon.
• Now enter the year into the newly created parameter. Depending on the value entered, the table that has been loaded will change.

STEP 9:

• To duplicate a created table, right-click on it and choose “Duplicate”. Re-right-click the replicated table and choose “Create Function” from the menu.

• Select the newly created function, enter the desired years, and select “Invoke”. It will generate a new table for each input.

STEP 10:

The data from the holiday analysis has now been successfully loaded and is ready to be used in the Power BI report. By include this option, valuable time can be saved over continually loading data from each year using traditional data loading techniques.

About Us:

Bi3 has been recognized for being one of the fastest-growing companies in Australia. Our team has delivered substantial and complex projects for some of the largest organizations around the globe and we’re quickly building a brand that is well-known for superior delivery.

Website: https://bi3technologies.com/

Follow us on,
LinkedIn: https://www.linkedin.com/company/bi3technologies
Instagram: https://www.instagram.com/bi3technologies/
Twitter: https://twitter.com/Bi3Technologies