BIBB
DEVX
•

PnP PowerShell for SharePoint for Power BI Users

#SharePoint #PowerShell #Data Automation #DevX #Power BI
Oscar MartĂ­nez
Oscar MartĂ­nez Data, BI and AI | Operational Lead | Power BI & Azure Expert | Governance-Driven Strategy | Product Owner
Website LinkedIn GitHub

As a Power BI professional, I often interact with SharePoint as one of the primary data sources. However, managing and manipulating SharePoint data can become challenging, especially when dealing with large data sets.

In this blog post, I will dive into a script I created that serves two purposes: start with PnP PowerShell for SharePoint for Power BI users and create test (and production) SharePoint lists.

Now let’s walk through the script that creates a new SharePoint list, fills it with random elements, counts them, and deletes them.

PnP PowerShell for SharePoint for Power BI users - complete implementation code

PnP PowerShell for SharePoint for Power BI users Script Breakdown

1. Opening PowerShell

I always run the PowerShell ISE (x86) version as an administrator.

Opening PowerShell ISE

Opening PowerShell ISE

2. Installing PnP PowerShell

If this is your first time using PowerShell, you must install it.

This command installs the PnP PowerShell module, which provides all the necessary cmdlets for managing SharePoint Online.

3. Define Variables

In PowerShell, variables are defined using a dollar sign $ followed by the variable name. There’s no need to declare the variable type; PowerShell will automatically determine the type based on the assigned value.

4. Connecting to SharePoint

In this section, we first connect to the SharePoint site using Connect-PnPOnline. The -UseWebLogin parameter specifies that we should authenticate using a browser pop-up. However, there are other authentication methods as well. For instance, if we have a username and password, we could use the -Credentials parameter. There are many other parameters available, such as -Interactive for an interactive login experience, -PnPManagementShell to use the PnP Management Shell multi-tenant application, and -SPOManagementShell to use the SharePoint Online Management Shell multi-tenant application.

5. Creating a New List

The New-PNPList command creates a new list in SharePoint using the specified list name and the “genericlist” template. The -OnQuickLaunch flag adds the list to the site’s quick launch for easy access.

6. Adding Fields to the List

In this script portion, we add new fields to the SharePoint list using Add-PnPField. The -Type parameter determines the data type of the new field. In our case, we are creating text fields for “Color” and “Animal” and a user field for “Owner”.

SharePoint supports several types of fields, some of which include:

  • Text: A basic text field
  • Note: A field for longer texts
  • Number: A field for numeric values
  • Currency: A numeric field that represents currency
  • DateTime: A field for date and time
  • Choice: A field where the user can choose from a pre-defined set of choices
  • Lookup: A field that allows users to look up values from another list
  • Boolean: A field representing True/False values
  • User: A field to represent a user or group
7. Filling in the List with Random Elements

We use a for loop to populate the list with random elements. In each iteration of the loop, Get-Random is used to pick a random ‘Color’ and ‘Animal’ from predefined choices. Then Add-PNPListItem creates a new list item with these random values and a static ‘Owner’ value.

The $i -le $total condition in the for loop specifies that the loop will continue as long as $i is less than or equal to $total. As $i increments with each iteration, the loop will run exactly $total times.

The Write-Progress command visually represents the script’s progress in the PowerShell console. It uses the calculated $currentProgress percentage to update the progress bar after adding each new item.

8. Deleting All Elements from the List

We delete all items from the list using the Remove-PnPListItem command. We use a foreach loop to iterate through all items in the list.

The try block contains the code that could cause an exception, such as when trying to delete an item that doesn’t exist. If such an exception occurs, the code in the catch block is executed. In this case, it writes an error message to the console.

By utilising try and catch, we can handle errors gracefully, ensuring our script can continue running even when minor errors occur.

9. Deleting the List

Finally, we will delete the list; for this, we use Get-PnPList to verify if the list exists, and if it does, Remove-PnPList to delete the list.

Key Benefits for Power BI Users

This script provides several advantages for Power BI professionals:

  1. Automated Test Data Creation: Quickly generate SharePoint lists with sample data for testing Power BI reports
  2. Consistent Data Structure: Ensure your test environments have predictable data structures
  3. Easy Cleanup: Remove test data and lists when no longer needed
  4. Scalable: Easily adjust the number of items created by changing the $total variable
  5. Error Handling: Robust error handling ensures the script continues running even if individual operations fail

Conclusion

PnP PowerShell is a powerful tool for Power BI professionals who work with SharePoint data sources. This script demonstrates how to automate common SharePoint management tasks, making it easier to create test environments and manage data for your Power BI projects.

Whether you’re setting up test data for development or managing production SharePoint lists, these PnP PowerShell techniques will help streamline your workflow and ensure consistent, reliable data management practices.

Thank you for taking the time to read this post. I hope that the information provided has been helpful and informative. Please feel free to share your thoughts and experiences in the comments section below.