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.