Excel Office Script to Add table in Excel Worksheet

Below is the Office Script to create table in Excel Worksheet. You can make it reusable and pass the table name as parameter.

function main(workbook: ExcelScript.Workbook, tableName: string) {
    let table: ExcelScript.Table
    let sheet = workbook.getActiveWorksheet();

    sheet.getAutoFilter()?.remove()
    const usedRange = sheet.getUsedRange()
    const rowCount=usedRange.getRowCount()
    const colCount=usedRange.getColumnCount()

    if(sheet.getTables().length<1){
        const tableRange = sheet.getRangeByIndexes(0,0,rowCount,colCount)
        table=sheet.addTable(tableRange, true)
        table.setName(tableName)
    }
}

Add Filters in Power BI

Filtering data effectively is crucial for creating interactive and user-friendly Power BI reports. One of the most efficient ways to filter data in Power BI is by using slicers. Slicers offer a straightforward and intuitive way to filter information directly on the report canvas. In this blog post, we’ll guide you through the steps to add a slicer, include a column, and modify the settings to use a dropdown menu.

Below are the steps to add Filters using Slicer in Power BI

• Open Your Power BI Report in Power BI Desktop: First, open your Power BI Desktop and load the report you want to enhance with filters.
• Add a Slicer: To add a slicer to your report:
  • Navigate to the Visualizations pane on the right-hand side.
  • Click on the Slicer icon. This will add a blank slicer to your report canvas.

• Select the Column for Filtering: Next, you need to specify which column you want to use for filtering:
  • In the Fields pane, expand the table that contains the column you want to use.
  • Drag the desired column to the slicer you just added. This column will now populate the slicer with its values.

The result of above steps will be in below format. Column and Data will vary as per your Data Source.

• Change Slicer Settings to Dropdown: By default, slicers in Power BI appear as Vertical lists. However, you can change the slicer to a dropdown for a more compact and user-friendly design:
  • Select the slicer on your report canvas.
  • In the Visual pane, you will see a dropdown under the Slicer settings section.
  • Click on the dropdown option to switch the slicer from a list to a dropdown menu.
  • You can also change other style settings like height, width, shadow, border etc. from Size and style section in Visual pane.

The Slicer will be in Dropdown format can be used to filter other visualizations.

Benefits of using Slicers in Power BI

• User-Friendly Filtering: Slicers provide an intuitive way for users to filter data without needing advanced knowledge of Power BI.
• Enhanced Interactivity: Slicers make reports more interactive and engaging, allowing users to explore data dynamically.
• Compact Design: Using a dropdown instead of a list saves space on the report canvas and makes the report look cleaner.

We hope this guide helps you create more dynamic and user-friendly Power BI reports. Happy reporting!

Add Power BI to Power Apps without White Spaces

Often, we aim to incorporate Power BI reports within Power Apps to enhance the user experience. Typically, this involves using the Power BI Tile to embed dashboard tiles. However, this approach often includes unnecessary white space around the tile, which detracts from the overall presentation.

Fortunately, there’s a straightforward method to eliminate this white space without relying on a dashboard. All you need is the Power BI Report itself. Here’s how to do it:

• Add a Power BI Tile to Your Power Apps Screen: Begin by navigating to your Power Apps screen and including a Power BI Tile. The crucial element here is the TileURL property of the Power BI Tile.
• Generate the TileURL:
  • Open your Power BI Report using the Power BI Service.
  • Navigate to Files > Embed report > Website or portal.
  • Copy the URL provided.

• Construct the URL for the TileURL Property:
  • Use the following format for the TileURL property: <Embedded URL from above step>&navContentPaneEnabled=false&pageName=<Section ID>
• Retrieve the Section ID: Each section or page within a Power BI Report has a unique ID, which you can obtain from your browser. Follow these steps:
  • Click on the desired report section within Power BI Report.
  • The URL will appear as follows: https://app.powerbi.com/groups/<group_id>/reports/<report id>/<section id>?experience=power-bi.
  • Extract the Section ID from this URL.
• Final Step: Combine the section ID with the previously copied URL to create the final URL. Insert this URL into the TileURL property of the Power BI Tile within Power Apps.

By following these steps, you can successfully embed a Power BI report in Power Apps without the unwanted white space, resulting in a cleaner and more professional user interface.

Administration Mode in Power Platform Admin Center

You can set a sandbox, production, or trial (subscription-based) environment in Administration Mode so that only users with System Administrator or System Customizer security roles will be able to sign in to that environment. Administration mode is useful when you want to make operational changes and not have regular users affect your work, and not have your work affect end users (non-admins).

Below are the steps to put an environment into Administration Mode.

• Go to https://admin.powerplatform.microsoft.com/
• Click on Environments on left navigation bar
• Click on the environment where you want to activate the Administration Mode
• Click on Edit of the Details section

• Scroll down. Under Administration Mode, toggle the button to Enable. Click Save once done.

Environment Management Control

You can control who can create and manage environments in Power Platform Admin Center. Below are the steps:

• Go to Power Platform Admin Center (https://admin.powerplatform.microsoft.com).
• Click on Settings gear on top right corner –> click on Power Platform Settings

Schedule Environment Update in Power Platform

Are you looking for a way to update your Environment in Power Platform??

You have the option to indicate your preferred update and feature delivery frequency for specific Microsoft Power Platform services for an environment. You have two options from which you can choose after creating the environment: Frequent and Moderate

• Service: Canvas App authoring
• Settings:
  • Frequent: Get access to latest updates and new features multiple times in a month
  • Moderate: Get access to updates and features only once a month

Add new item in the List

You can add a new in item in the existing list by using Add item to list action.

I added a new item in employeeDetails list.

Create List in Power Automate Desktop

Create new list action is used to create a new list which is empty. You can rename the variable.

I have created a new list employeeDetails

Increase/Decrease variable value in Power Automate Desktop

There might be a need to increase or decrease the value of a variable in many cases, like loops. Search for the respective actions in Action pane.

Generate Random Numbers in Power Automate Desktop

Generate random number action is used to generate random numbers within a given range.

Select the Generate random number action from Actions pane. Drag and drop it to Workspace area. It will open a popup window to enter the parameters related to the action.