Skip to main content

Posts

Showing posts from March, 2021

[Microsoft Flow] How to Connect Microsoft Form Responses to Power BI

Requirement : Auto-import of data to Power BI when a new response in Microsoft Forms is submitted. Solution : Connect Microsoft Forms to Power BI through Microsoft Flow. Assumptions : Power BI is already installed. Microsoft Form is already created. Creating a Streaming Dataset 1. Sign into https://powerbi.microsoft.com. 2. On the Power BI homepage, click New under 'My workspace'.  3. Select Streaming dataset . 4. Select API and click Next . 5. Enter a name for your dataset. 6. Enter each question from MS Forms to 'Values from stream' in Power BI and select the type of the answer for each. 7. Click Create . 8. Turn on Historic data analysis and click Done . 9. When you click the name of your dataset under 'My workspace', you are now ready to create a Power BI report. Connecting MS Forms to Power BI 1. Log into https://flow.microsoft.com. 2. Click My flows in the left navigation bar. 3. Click New flow and select Automated cloud flow . 4. Before you create yo...

[SharePoint] Creating a SharePoint Survey

  1. On your SharePoint homepage, click the settings icon located on the upper right corner of the page. 2. Click Add a page. 3. Type 'survey' in the search bar. Select Survey . 4. Enter a name for your survey. Click Create . 5. Your survey will appear in the gallery. 6. When you click the survey, you will be redirected to the survey page.  7. Click Settings and select Add Questions. 8. Type your survey question and select the type of answer. 9. Click Next question to add more questions for your survey. 10. Once you are done adding all the questions, click  Finish . 11. Provide the link to the responders and ask them to click on  Respond to this Survey to start the survey. 12. You will see more survey options by checking the 'Survey Settings'.

[Power BI] Connecting SharePoint Excel File to Power BI (Web version)

Requirement : Auto-update of Power BI report when an excel file in SharePoint is updated. Assumptions : Power BI is already installed. User has general knowledge in SharePoint and Power BI. Level : Beginner Connecting SharePoint Excel File 1. Sign into  https://powerbi.microsoft.com . 2. On the Power BI homepage, click Get data located on the lower left corner of the page.  3. You will be given options where to get the data you will use in your report. Select  Files . 4. From the displayed options, select SharePoint - Team Sites. 5. You will be asked to enter the URL to connect to. Enter the root URL of the SharePoint site where the excel file you will use is saved. 6. The list of SharePoint folders will be displayed.  7. Select the excel file from the SharePoint list. Click Connect . 8. Click Import . 9. On the homepage, click Create and select Pick a published dataset. 10. Select the file that you want to use from the list and click Create . 11. You are now ready...

[Excel] How to Protect Multiple Excel Tabs with Different Passwords

Requirement:  I have an excel file with multiple tabs which is being updated monthly by many users. I want to create a password for each tab so that each user can only view and edit their own tab. Limitation : Protect Sheet Function in Excel is notoriously weak.  Solution : Use VBA code to set up password on each tab. Protecting Multiple Tabs with Different Passwords Let's say I have three teams which need to report their daily sales in one excel spreadsheet. The three teams are named Team Apple, Team Watermelon and Team Grapes. First, let's create a new tab for each one of them. 1. Add three new tabs and rename each according to the team name. 2. Create a column for 'Name', 'Item', 'Sales' and 'Date' for each team.  3. Click Visual Basic from the 'Developer' tab. 4. From Sheet 2 to Sheet 4, copy and paste the VBA code below: VBA Code: Private Sub Worksheet_Activate()     Dim strPassword As String     'On Error Resume Next        ...

[Microsoft Flow] Different Types of Cloud Flow

There are three different kinds of Cloud Flow : 1. Automated Flow - automation is triggered by an event.       Examples :  When you receive an email When someone submitted a response in MS Forms When someone added, remove or modify an item in SharePoint When these events occur, the flow will be triggered automatically and the action will be executed. 2. Instant Flow - automation is triggered with a click of a button.     Examples :  When you need to send a reminder for the team When you need to review and approve proposal When you need to share a post in your social media accounts The flow is triggered manually or on-demand when the users click a button. 3. Scheduled Flow - automation is triggered on a certain data/time or frequency set by the user.      Examples : When you need to upload data to a database When you need to send an email When you need to create a dynamic record

[Microsoft Flow] Send Email Notification to Multiple Users When a New Response is Submitted

Requirement : Send email notification to multiple users when a new response in Microsoft Forms is submitted. Limitation : When a Microsoft Form response is submitted, only the owner and the responder will be notified. Solution : Use Microsoft Flow to send notifications to multiple users. 1. Log into https://flow.microsoft.com . 2. Click My flows in the left navigation bar. 3. Click New flow and select Automated cloud flow. 4. Before you create your flow, click on 'Untitled' and enter a name for your flow. 5. From the list of apps and tools, select Microsoft Forms. 6. Select the trigger for the flow which is 'When a new response is submitted'. 7. Click on the Form ID box and pick a form that you want to use from the list. 8. Click New Step. 9. Enter 'apply to each' in the search box and select Apply to each control in the 'Actions' tab. 10. Select Response Id from the 'Add dynamic content' menu. 11. Click Add an action. 11. Enter 'get resp...