In Power BI dataset, you could use PowerShell to refresh the dataset. Here is the sample script to refresh Here is the script to refresh dataset named “MyReport” inside workspace named “My Workspace”.  

$User = "admin@mycompany.com"

$PW = "password"

$SecPasswd = ConvertTo-SecureString $PW -AsPlainText -Force

$myCred = New-Object System.Management.Automation.PSCredential($User,$SecPasswd)

Connect-PowerBIServiceAccount -Credential $myCred

# Get Power BI Workspace ID

$WSID = Get-PowerBIWorkspace  -Scope Organization -Name 'My Workspace'| ForEach {$_.Id}

# Get dataset ID

$DSIDRefresh = Get-PowerBIDataset -Scope Organization -WorkspaceId $WSIDAdmin | Where {$_.Name -eq "MyReport"} | ForEach {$_.Id}

$RefreshDSURL = 'groups/' + $WSIDAdmin + '/datasets/' + $DSIDRefresh + '/refreshes'

$MailFailureNotify = @{"notifyOption"="MailOnFailure"}

Invoke-PowerBIRestMethod -Url $RefreshDSURL -Method Post -Body $MailFailureNotify

Please note after refresh done from PowerShell, if you look at the refresh history, you will see the refresh  type is empty. It seems like Power BI could not decide if the refresh is done by on demand or Scheduled.

PowerShell script to update Power BI Dataset parameters

In Power BI dataset or reports, parameters serve as a way to easily store and manage a value that can be changed after deployed. You could set up the parameters like database server name and database names inside Power BI solution and update through Power BI Settings as discussed before.

In this blog, we will use PowerShell to update the parameters. Here is the script to update the following two parameters on dataset named “MyReport” inside workspace named “My Workspace”. 

# Install-Module -Name MicrosoftPowerBIMgmt

# Connect to Power BI As admin

$User = "admin@mycompany.com"

$PW = "Password"

$SecPasswd = ConvertTo-SecureString $PW -AsPlainText -Force

$myCred = New-Object System.Management.Automation.PSCredential($User,$SecPasswd)

Connect-PowerBIServiceAccount -Credential $myCred

# Get Power BI Workspace ID

$WSID = Get-PowerBIWorkspace  -Scope Organization -Name 'My Workspace'| ForEach {$_.Id}

# Get Power BI Dataset ID

$DSIDMyReport = Get-PowerBIDataset -Scope Organization -WorkspaceId $WSIDAdmin | Where {$_.Name -eq "MyReport"} | ForEach {$_.Id}

# Construct Url with Dataset ID

$urlUpdateParams = "/datasets/" + $DSIDMyReport + "/Default.UpdateParameters"

# Update Parameters Body

$body = '{

  "updateDetails": [

    {

      "name": "DatabaseName",

      "newValue": "NewDBNamev"

    },

    {

      "name": "ServeName",

      "newValue": "myserver.database.windows.net"

    }

  ]

}'

$content = 'application/json'

# Update Dataset Parameters

Invoke-PowerBIRestMethod -Url $urlUpdateParams -Method Post -Body $body -ContentType $content

As enhancement, you could also use service principal as discussed in here to facilitate the login.

How to configure Power BI dataset to connect to different databases in different stage workspaces?

When working in Power BI deployment pipeline, different stages may have different configurations. For example, each stage can have different databases. DEV workspace Power BI can connect to dev database while PRD workspace Power BI will connect to PRD database. How could you deploy Power BI content in different stage workspaces that will connect to different databases automatically? Here are the steps to configure.

First, make the database serve name and password as parameters inside Power BI. This will enable to configure them in the next steps.

You can go to Power BI desktop “Home”->”Transform data”-> ”Transform data”->”Manager Parameters”->”New Parameter”. Enter the parameter as the screenshot below. Please note you could enter a list of values for one parameter. See reference for details.

Second, configure Power BI connection to use the parameters. When you select database, select the parameters as below screenshot.

Third, configure table to store the parameters and cards to display the parameters. Add this page to the report so you could use it to debug if there is data issue.

You can go to Power BI desktop “Home”->”Transform data”->Right click the Parameter->”Convert To Query”. Then the parameters will be stored in a table like below.

Forth, deploy the Power BI .pbix file to workspace and identify if the parameter is displayed in Power BI workspace setting.

You can go to Power BI workspace->”Datasets + dataflows”->”More options”->”Settings”->”Parameters”. Verify the parameter values. You could also change the value but need to refresh the dataset afterword to pick up the right parameter. See more here.

At this point, you already have a configurable database connection parameters you could change in the workspace. However, this is still manual process. The next few steps will automate the process.

Fifth, create a pipeline and create different workspaces as different environments like DEV, TEST, and PRD. In our example, we created three environments like below. For details to create pipeline, please refer Microsoft Power BI Pipeline document.

Sixth, configure the Dataset rule for each environment. The parameter will change to the value configured in the rule automatically!

• In the pipeline stage you want to create a dataset rule for, select Deployment settings.
• From the Deployment settings pane, select the dataset you want to create a rule for.
• Select the type of rule you want to create, expand the list, and then select Add rule.

Now, you could configure the parameter to use DEV and deploy to DEV workspace. Then use pipeline to deploy to TEST workspace. Verify the parameter changed to TEST database automatically. The use pipeline to deploy to PRD workspace. The database will automatically point to PRD now!

Please note pipeline deployment is different from .pbix file. After .pbix file deployed, it will create two components. One for report and another for dataset. In pipeline deployment, you will need to select BOTH report and dataset and then deploy! If you only select the dataset, the report will not be deployed.

Power BI new column based on other table

 We have a Power BI report need to get the total "Approver Tasks" for one submitted "PubsRecord". There are few options and the best way is to calculate on the database view if possible. If you do not have option to modify database, you could use Power BI calculation. 

If you need to have multiple filter and conditions, certain format need to be implemented. Here are two proposals and one will have error.

This one is correct one.

Number Approval Tasks =

IF(

    CALCULATE (

        COUNTROWS ( DimApprovalTasks ),

 DimApprovalTasks[LatestTask]= "Yes",

        FILTER ( DimApprovalTasks, DimApprovalTasks[PubsKey] = FACTPubsRecord[PubsKey] )

    )= BLANK(),

    0,

    CALCULATE (

        COUNTROWS ( DimApprovalTasks ),

        DimApprovalTasks[LatestTask]= "Yes",

        FILTER ( DimApprovalTasks, DimApprovalTasks[PubsKey] = FACTPubsRecord[PubsKey] )

    )

)

 

This one will have error.

Number Approval Tasks =

IF(

    CALCULATE (

        COUNTROWS ( DimApprovalTasks ),

        FILTER ( DimApprovalTasks, DimApprovalTasks[PubsKey] = FACTPubsRecord[PubsKey] ),

        FILTER ( DimApprovalTasks, DimApprovalTasks[LatestTask]= "Yes"),

    )= BLANK(),

    0,

    CALCULATE (

        COUNTROWS ( DimApprovalTasks ),

        FILTER ( DimApprovalTasks, DimApprovalTasks[PubsKey] = FACTPubsRecord[PubsKey] ),

        FILTER ( DimApprovalTasks, DimApprovalTasks[LatestTask]= "Yes"),

    )

)

You will notice the combination of "Filter" and other conditions will give you the flexible way to get calculation against any other tables. 

Power BI Dataset Refresh Error for Column in Table contains a duplicate value

We have a Power BI dataset and report running for few month without refresh error. However it shows the following error and refresh stops.

Column 'PubsID' in Table 'FACTPubsRecord' contains a duplicate value '1115' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.

After debugging the database tables, table relationships, columns, and measures, we were not able to resolve the issue. I started to rebuild the dataset and everything looks good. At the end when I document the dataset and set up the properties of the table, this is one "Key column" identified that is cuasing the problem. The column named PubsId that was unique before. However, it is no longer unique.

The Key Column in Power BI properties MUST be unique in order for Power BI refresh to work. The solution is to change it to PubsKey unique value to resolve the issue.

This is hard lesson that took me two days to finally resolve the isuse.

How to Sort by Month Year Name in Power BI report

When you create Power BI report based on Month and Year calendar, the default sorting i s based on the alphabetic name like then screenshot below.

If we need to create a report that will display the month and year combination and sorted by the time, you will need to create a different column and apply the sorting. Here are the steps.


First you will need to crate a new column like below:
MonthYearOrder = CONCATENATE(MyCalendar[Year],right(CONCATENATE("00",MyCalendar[MonthNumer]),2))

Then click the column you need to display like "MMM_YYYY" and select "Sort by column". Pick the new column "MonthYearOrder" just created.

In the report, you will need to pick either acceding or descending to display the sequence correctly.

You could use this method to apply the sorting on other columns like "Department" based on the different order you prefer to display.

Power BI Streaming Dashboard example

There are many different ways to present real data report and dashboard in Power BI. One of the way is to use PubNub that could make your data streams available on the Microsoft Power BI platform.  Here is the detailed steps to use PubNub for Power BI.

The first step is to create a Power BI dataset from Power BI service.

The second step is to create the dataset by connecting to PnbNub. The data example is on the PnbNub site. You need both Channel and Subscription Key to connect.

The third step is to create the dashboard from Power BI service. You should select the "Streaming dataset".

The forth step is to add tiles to the report as in the below screenshots.

There are some gotchas you should be aware of the Power BI streaming. Please read Microsoft blog for details.

There are few different ways for Power BI streaming, you could also use Flow automation to push data to Power BI dataset.

Create summary table and ensure no empty value for Power BI

If you need to create a summary table for Power BI report, you should be careful not to return empty value. Here is one example.

NoActionTakenApprovalTasks = SUMMARIZE(DimApprovalTasks,
   DimApprovalTasks[Approver],
   DimApprovalTasks[ApprovalRole],
   "Total Tasks", countrows(DimApprovalTasks),
   "Total No Action Taken Tasks",  IF(
CALCULATE(COUNTROWS(DimApprovalTasks), DimApprovalTasks[Status]= "No Action Taken")= BLANK(),
0,
CALCULATE(COUNTROWS(DimApprovalTasks), DimApprovalTasks[Status]= "No Action Taken")
)
)


In this example, we add an IF condition to return either 0 or value if not empty. This will be same to create measure to ensure value set to 0 when empty value returned. An example is below.

Total Approved Tasks =

IF(

CALCULATE (

COUNTROWS ( DimApprovalTasks ),

DimApprovalTasks[Status]= "Approved"

) = BLANK(),

0,

CALCULATE (

COUNTROWS ( DimApprovalTasks ),

DimApprovalTasks[Status]= "Approved"

)

)

Power bi calculated column multiple if statements example

We have a requirement to display the percentage distribution in Power BI. The percentage can be any value from 0% to 100%. However, the report should show the percentage range like 10-20, 20-30, etc.

In order to do this, a calculated column with multiple if statements will be required. If you know the syntax, it will be quite simple. Here is the example.


Percentage Range =
IF([No Action Taken %]=0.00,"0",
 IF(AND([No Action Taken %]>0.00, [No Action Taken %]<=0.10),"1-10",
  IF(AND([No Action Taken %]>0.10, [No Action Taken %]<=0.20),"11-20",
    IF(AND([No Action Taken %]>0.20, [No Action Taken %]<=0.30),"21-30",
      IF(AND([No Action Taken %]>0.30, [No Action Taken %]<=0.40),"31-40",
        IF(AND([No Action Taken %]>0.40, [No Action Taken %]<=0.50),"41-50",
          IF(AND([No Action Taken %]>0.50, [No Action Taken %]<=0.60),"51-60",
            IF(AND([No Action Taken %]>0.60, [No Action Taken %]<=0.70),"61-70",
     IF(AND([No Action Taken %]>0.70, [No Action Taken %]<=0.80),"71-80",
   IF(AND([No Action Taken %]>0.80, [No Action Taken %]<=0.90),"81-90",
IF(AND([No Action Taken %]>0.90, [No Action Taken %]<1),"91-99", "100"
 
)))))))))))

There is another option to use switch you could also try.

Populate user office location for Power BI

There is a need to populate user office location for Power BI. Here is one of the quickest way to get this information trough AD Powershell.

I've quickly developed a script to have user office populate in a csv file and upload to SharePoint for Power BI to consume. Here is the script based on AD Powershell.


$ADUsers =  Get-ADUser -Filter *  -Properties physicalDeliveryOfficeName  | where physicalDeliveryOfficeName  -ne $null
$outputFile = "C:\Harry\Projects\SPCOE\Scripts\GetADUsers\Output\ADUsers.csv"

$rows = @()

foreach($ADUser in $ADUsers)
{
    $rows += New-Object -TypeName PSObject -Property @{                                                                                                                                   

                                            Name = $ADUser.Name
                                            UPN = $ADUser.UserPrincipalName 
                                            Office = $ADUser.physicalDeliveryOfficeName                                       

                                            } | Select-Object Name,UPN,Office

}

$rows | Export-Csv $outputFile  -NoTypeInformation -Force  -ErrorAction SilentlyContinue

Power BI for LMS365 learning data

In order to get learning data from cloud LMS365, you could use either connector for Microsoft products like Logic Apps, Power Automate, and Power Apps or OData web services directly. For Power BI, you could use OData with API Secret Key for your tenant. The steps have been described in details by Microsoft. There are few tips you should be aware of.

1. You need to get the API Secret Key for your tenant from the support before you can use the API.
2. The user name normally is “api”
3. After connected to LMS365 through OData, you should not import all tables. You will get an error when you importing all tables.
4. Normally you only need the following enties.

• Course Catalog
• Course
• User
• Enrollment
• Assignment
• SCORM
• SCORM Statuses
• Quiz User Statuses
• Learning Module User Statuses

You should set up the refresh schedule using the same OData credential. The example of the report is below.

How to invoke Azure function inside PowerShell or Azure Runbook

The way PowerShell to call Azure function is same as call rest web service. There are few tips as below.

1. Use client object module and add the module into PowerShell or Azure Runbook
2. Azure function might have security configured and you will need to add "Access-Control-Allow-Origin"
3. You might need to pause the result but in our case it is simple "Yes" or "No"

The example below can give you quick start.

# Add references for Runbook

Add-Type -Path  "C:\Modules\User\Microsoft.SharePoint.Client\Microsoft.SharePoint.Client.dll"

Add-Type -Path  "C:\Modules\User\Microsoft.SharePoint.Client\Microsoft.SharePoint.Client.Runtime.dll"

$spoSiteUrl ="https://yourcompany.sharepoint.com/sites/pubs"

$creds = Get-AutomationPSCredential -Name 'O365_svc'

# Load SharePoint online CSOM for email

$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($spoSiteUrl)

$userName = $creds.UserName

$password = $creds.Password

$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userName, $password)

$ctx.Credentials = $credentials

$ctx.AuthenticationMode = [Microsoft.SharePoint.Client.ClientAuthenticationMode]::Default

$PubsId=12345

$RequestUrl='https://yurcompany.azurewebsites.net/api/PublicationLookup?code=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx==&qry=canbepartiallyapproved&id='+$PubsId

write-Output $RequestUrl

$WebSession = New-Object Microsoft.PowerShell.Commands.WebRequestSession

$WebSession.Credentials = $Context.Credentials

$WebSession.Headers.Add("Accept", "application/json;odata=verbose")

$WebSession.Headers.Add("Access-Control-Allow-Origin", "mycompany.sharepoint.com")

 

$Result="No"

#Invoke Rest Method

$Result = Invoke-RestMethod -Method Get -WebSession $WebSession -Uri $RequestURL

write-Output $Result $pubs.Id 

Tips to filter Power BI report using query string parameters in the URL

With increasing usage of Power BI report, there is a case that users request to have a link on the SharePoint list to query all related information on the item. One example is as below.

One event list with all different events

Another publication list that have look up column linked to the event

The requirement is to have a link to the event list item that will display ALL the publications related to that event. The solution is to Filter a Power BI report using query string parameters in the URL as the screenshot below.

Here are some tricks and tips.

1. Filter query string parameters only works on  Power BI service or apps. It does not seem to work on the URL embedded in SharePoint Online or WebSite.
2. The query can work for hidden page, hidden fields
3. Table and Field names are case-sensitive, value isn't.
4. The query syntax is different for data type like Number or String. You need to add '' to the string value
5. The last part in report URL like ‘?noSignUpCheck=1’ need to be removed before adding the filter
6. You might need to change the special characters like space to %20

The example is like below.

This one will not work before removing the ‘?noSignUpCheck=1’.

https://app.powerbi.com/groups/9d999dde-1b4e-abcd-abce-xxxxxxxxxxxxxx/reports/01e1eb9e-abcd-4567-b500-xxxxxxxxxxxxxx/ReportSection1c9c48720016d813ded1?noSignUpCheck=1?filter=FACTPubsRecord/PubsMeetingID%20eq%201549

This will work.

https://app.powerbi.com/groups/9d999dde-1b4e-abcd-abce-xxxxxxxxxxxxxx/reports/01e1eb9e-abcd-4567-b500-xxxxxxxxxxxxxx/ReportSection1c9c48720016d813ded1?filter=FACTPubsRecord/PubsMeetingID%20eq%201549

You could use this URL to send specific query to end users or add the calculated column to SharePoint list.

How to use REST API call to query SharePoint list over 5000 threshold?

We have a SharePoint SPFx project that is using SPFx JS and REST API to query few large SharePoint lists with over 5000 items. We got the following error:

Microsoft.SharePoint.SPQueryThrottledException
The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.

The two calls for SPFx JS and REST call are listed below.

https://mycompany.sharepoint.com/sites/pubs-stg/_api/web/lists/getbytitle('Journals')/Items?$select=ID&$filter=(ISDNNumber eq '0160-6999')

let spRecs = await web.lists.getByTitle(listName).items.filter("Publication_x0020_Record_x0020_I eq " + recordId).getAll();

After some testing, it seems like we could resolved this issue by adding the index. We looked at the two calls and both are using filter on the field "Publication_x0020_Record_x0020_I" lookup column to another list. The solution is to add the index to this field!

After index column "Publication_x0020_Record_x0020_I" , the calls will be successful. You could also add other fields to the filter as long as the first filter returns less than 5000 items. Here is the example:

https://mycompany.sharepoint.com/sites/pubs-stg/_api/web/lists/getbytitle('Journals')/Items?$select=ID&$filter=(ISDNNumber eq '0160-6999') and (Abbreviation eq 'AADE Ed J')