DAY 14 - Create a list in SharePoint > Upload Excel Data at SharePoint List > Access Online SharePoint List From POWER BI > Schedule the POWER BI Report

A list in SharePoint is a collection of data that gives you and your co-workers a flexible way to organize information. Add columns for different types of data, such as text, currency, or multiple choice. 

Moreover it can be accessed from POWER BI. Yeah, that's the only interesting point for me !!! So any person with appropriate permission can read or edit the data of SharePoint List.

Importing files from SharePoint Online List, is a great way to make sure the work you’re doing in Excel stays in-sync with the Power BI service.

I'll explain its connectivity with POWER BI in the last section of this blog. 

Advantage of SharePoint List :

1. It can be accessed from Data Visualization Tool like POWER BI ,Tableau etc.
2. You need to give access to a user and the user can access the SharePoint List.
3. Ability to create many different views where you can sort,filter or group by attributes/fields
4. Ability to set permission at row & item level
5. Ability to integrate with Flow & PowerApps
6. Ability to export data to Excel & many more ...............


1. Open share-point web-page and Navigate to Home > New > List to create a share-point list

2. A new pop up window will appear . Enter the name of List & click on Check box if you want it to be shown in site navigation.

3. Now you have multiple options to insert/upload data into the newly created Share-point List .      There is only one column in the list by default.
So we can go ahead and add columns as per client's requirement by clicking on "Add Column"

  > New                 
It's being used to insert a new record in the Share-point List

  > Quick Edit       
It's being used to edit existing data . This option can be used to copy-paste the data from excel to Share-point list.Just make sure columns in a Share-point List are in the same order as in Excel.

  > Export to Excel
A file "query.iqy" will be downloaded in your system as soon as you click on  this option.Data will get exported to excel once you launch the "query.iqy".

  > PowerApps       
 This functionality is being used to create an app which provides flexibility to end-user in searching/updating/inserting data into Share-point List Via a Custom Form (i.e. UI is very similar to Entry Form)

  > Automate
Create a flow to automate the data synchronization process


4. Now there is a problem in-front of us i.e. how do we upload bulk data into the list via an excel file. There is a simple solution for this problem.
You don't even need to go to Share-point web page. This can be done from MS-Excel software    itself. 
 
   a. Select the data in excel and press "ctrl + T" to format it as Table and Click OK

     

 
   b. Navigate to Export > Export To SharePoint List
     

   c. A new pop window would appear where you have to enter name of SharePoint Page where you           want to publish your table
     
     

 
   d. On the next screen, you will see all the columns with corresponding recognized column types.
       Just Click Finish
     

   e. Table is published successfully as a SharePoint List.
     

        Access it by clicking the link.
       
     

 

5.  Another approach to import data from Excel :
 
    Steps to import Excel data to a SharePoint list with the Import Spreadsheet app:

1. Click on the settings gear and select 'Add an app'.
2. Search for Excel and select the Import Spreadsheet app.
3. Give the app a name and browse to the Excel file you want to import.
4. Enter the URL of the SharePoint site to where you want to import the Excel table.
5. Click on Import, and you will be asked to log in to SharePoint again.
6. Select the table or range that you want to import and click on Import.

      Now the Excel data you selected will be imported to the SharePoint app.

https://support.office.com/en-us/article/create-a-list-based-on-a-spreadsheet-380cfeb5-6e14-438e-988a-c2b9bea574fa


How do we load data into POWER BI ?

SharePoint List URL : https://sites.xxyyzz.com/sites/RRSharePointDevelopment/Lists/COVID19/Allitemsg.aspx
 
1. Open POWER BI and navigate to Get Data > More > Online Services > SharePoint Online List

2. A pop up window will appear and you have to enter URL of SharePoint page and Click OK
   

 
3. Use your Microsoft account to login to SharePoint and Click on Connect.

 

4. A new pop up window will appear where you can see custom + system SharePoint List.
Select your list and click on Transform Data to transform the data as it has some inbuilt columns like Id, FileSystemObjectType etc.

5. Loaded data successfully into POWER BI after transforming it.  I could see that 'Transform Data'        option is still enabled. So we can do data-modeling further.

 

6. Save the POWER BI Report with a name - 'COVID19_B' and search for the dateset so that we can      schedule it

7.     Enter Credentials 

        Authentication Method Should be OAuth2 & Privacy Level should be Organizational

        Once your username is authorized,'Scheduled refresh' section would be enabled.

Yippee, we have scheduled the POWER BI report. So we don't need to refresh it manually. Any changes in SharePoint List would be refreshed daily at 10 AM.  

8.  Next step is to validate whether refresh works or not. So we are changing value of confirmed case       from 6,817 to 6820 for state - Maharashtra.  So this value should reflect at POWER BI report.

  

     We have already updated data at SharePoint List. Now we have to refresh the data-set. 

      

       

It's time to refresh POWER BI report to see the updated data.

#RR #Day14 #SharePointList #Excel #PowerBI #SchedulePowerBIReport #HappyLearning #WeLearnEveryday