Sunday, February 6, 2022

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. Enter the SharePoint URL 




    3. Paste the URL in Power BI Desktop







    4. Click on the Transform Data tab



    5. Filter the Excel file by using the Name column 





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



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


    8. Rename the Query 1 to Customer 




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






    10. Follow the steps for the remaining Excel worksheets. 


      1. Product List




        Regions 




        Sales 







    11. 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 






    12. Regions table 


      Regions table has a similar column as Sales order 






    13. 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






    14.  Data Relationships

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


       

      Same for OrderDate and Date table




        
    15. 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])


    16. 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 






    No comments:

    Low Code Reimagined with AI + Copilot Pitch Deck - Copy Copilot day (Virtual) - 2023

     Hi All,  I presneded a session at Pune UG on Low Code Reimagined with AI + Copilot Pitch Deck.  Video is at this address  https://www.youtu...