How to: Use SharePoint Online folders within Power BI Desktop

 Hi All,

Power BI has a lot of connectors and one of the connectors is the SharePoint Online folder. 

This can be used in 

Power BI (Datasets) 

Power BI (Dataflows)

Power Apps (Dataflows

Excel 

Dynamics 365 Customer Insights

Here is the video that Whizlabs posted. 

Here is step by step how you can use it. 

1. Power BI Desktop >> SharePoint Online Folder >>  Get Data >> SharePoint Folder
   
   
   
   
   
   
   
   

2.  Grab the SharePoint Online Site which has stored your excel files 
   
   

   

   
   
   

3. Enter the SharePoint URL 
   
   
   
   
   
4. Paste the URL in Power BI Desktop
   
   

   
   
   

   
   
   
   
   
5. Click on the Transform Data tab
   
   
   
   
6. Filter the Excel file by using the Name column 
   
   

   
   
   

   
   
   
7. Click on the double arrow which is parsing content from Excel 

   
   

   

   
   
   
8. Select the First Excel worksheet file i.e. Customer List 
   
   
   

   
9. Rename the Query 1 to Customer 
   
   

   
   
   
   
10. Delete the first column and perform  a simple transformation Use First Rows as Header 
    
    

    
    
    

    
    
    
    
11. Follow the steps for the remaining Excel worksheets. 
    
    
    

    1. Product List
       
       

       

       
       
       
       Regions 
       
       

       

       
       
       
       Sales 

    
    

    
    
    

    

    
    

    
    

    
    

    
    
12. Analyze Sale Table 
    
    
    

    
    

    Adjust the Date Format

    
    

    

    
    

    Order Date table has information from 2015 to 2016 

    
    

    
    
    

    Delivery Region of Sales table has each USA states short names 

    
    
    

    

    
    
    
    

    
    
13. Regions table 
    
    
    Regions table has a similar column as Sales order 
    
    

    

    
    
    
    
    
    
14. Why do we need a Date table? 
    
    📝 Dimension tables (Like Date table) are used to save #space and make everything work more effectively
    📝 Adding columns such as date, day name, month name, month number, year, day number, quarter, etc within the Fact table is inefficient.
    📝Builtin  #time #intelligence requires a date table
    📝Ability to #slice and #dice by many date attributes such as week number, half-year, day of the year, etc
    
    

    
    
    

    It opens in a new window. 

    New Source >> Blank Query
    
    

    
    
    

    
    Advanced Editor 
    
    

    
    
    Paste the Date Table code within Advanced Editor 
    
    

    let fnDateTable = (StartDate as date, EndDate as date, FYStartMonth as number) as table =>

      let

        DayCount = Duration.Days(Duration.From(EndDate - StartDate)),

        Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),

        TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),   

        ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),

        RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),

        InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]),type text),

        InsertYearNumber = Table.AddColumn(RenamedColumns, "YearNumber", each Date.Year([Date])),

        InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),

        InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type text),

        InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),

        InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),

        InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM"), type text),

        InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),

        InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),

        InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),

        InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text),

        InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),

        InsertWeekNumber= Table.AddColumn(InsertWeekEnding, "Week Number", each Date.WeekOfYear([Date])),

        InsertMonthnYear = Table.AddColumn(InsertWeekNumber,"MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100),

        InsertQuarternYear = Table.AddColumn(InsertMonthnYear,"QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100),

        ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{"QuarternYear", Int64.Type},{"Week Number", Int64.Type},{"Year", type text},{"MonthnYear", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfMonth", Int64.Type}, {"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthInCalendar", type text}, {"QuarterInCalendar", type text}, {"DayInWeek", Int64.Type}}),

        InsertShortYear = Table.AddColumn(ChangedType1, "ShortYear", each Text.End(Text.From([Year]), 2), type text),

        AddFY = Table.AddColumn(InsertShortYear, "FY", each "FY"&(if [MonthOfYear]>=FYStartMonth then Text.From(Number.From([ShortYear])+1) else [ShortYear]))

    in

        AddFY

    in

        fnDateTable

    
    
    

    

    
    
    

    

    
    Once Invoke is clicked, rename the table form Invoked function to Date
    
    

    

    
    
    

    
    
    
    
15.  Data Relationships
    
    

    Link the Sales Order Table (DeliveryRegion Column)  with the Region table (Short Code Column) 
    
    

    

    
     

    
    

    Same for OrderDate and Date table
    
    
    

    
    

    

    
      
16. Create DAX Calculations 
    
    You may have many DAX in your business. It's best to place all your DAX measures within the DAX table to maintain. 
    

    a) Created Empty Table 

    
    

    
    
    

    b) In new Key Measures table  >> Create a New Measure

    

    
    

    
    
    

    Creare 4 measures within Key Measures table 

    
    

    1. Total Sales = SUM('Sales Order'[LineTotal])

    2. Units Sold = SUM('Sales Order'[OrderQuantity])

    3. Unique Order  = COUNTROWS('Sales Order')

    4. Average Sales per day = AVERAGEX( VALUES( 'Date'[Date] ), [Total Sales])
    
    
    
17. Creating Visuals 
    
    a) Date and Total Sales 
    
    Drag the Date column from the Date table under Visuals. Set the Date column instead of Date Hierarchy 
    
    

    
    
    Drag the Total Measure
    
    

    
    
    
    Change to Stacked Column Chart  
    
    
    

    

    
    
    
    

    
    
    b) Copy Cards Visuals 
    
    
    

           1. Sales

    2. Unique Order

    3. Units Sold

    4. Average Sale per Day
    
    

    

    
    
    
    c) Donut Chart
    
    1. Donut Chart - (Customer) Distribution Channel VS (Measure) Total Sales
    
    

    Formatting 

    a) Legend Off

    b) Details Label (Category, Value) 

    c) Title (Sales by Distribution ) 
    
    

    
    
    

    

    
    
    

    
    
    

    
    2.  Donut Chart - (Sales) Warehouse Code VS (Measure) Total Sales 
    
    

    

    
    
    
    3. Donut Chart - (Regions) Region vs (Measure) Total Sales
    
    

    

    
    
    
    
    d) MAP 
    
    

    Location: Name

    Legend: Region

    Size: Total Sales
    
    

    

    
    
    

    
    e) Date Filter 
    
    

       

    1. Drag Year from Date Table.

    2. Table format.  Change to the slicer.

    4. Slicer Format >> General >> Orientation >> Horizontal

    
    

    

    
    
    

Enjoy your day. 

--aaroh