Showing posts with label SharePoint Online. Show all posts
Showing posts with label SharePoint Online. Show all posts

4/30/2019

SharePoint.Tables – Power BI – Power Query M Functions


The Microsoft DOCS article for SharePoint.Tables says "Returns a table containing a row for each List item found at the specified SharePoint list url." While this sounds like it returns list "Items", it actually returns a list of Lists. It also reads like the URL is supposed to point to a list. Instead, it should point to the site or subsite.
If this was a typical DOCS article, I'd click the Edit button and offer an update to the content. For some reason, there is no Edit button. So here is my proposed revision:

Syntax

SharePoint.Tables(**url** as text, optional **options** as nullable record) as table
 

About

Returns a table containing a row for each List item found at the specified SharePoint list site url. Each row contains properties of the a List. options may be specified to control the following options:
  • ApiVersion : A number (14 or 15) or the text "Auto" that specifies the SharePoint API version to use for this site. When not specified, API version 14 is used. When Auto is specified, the server version will be automatically discovered if possible, otherwise version defaults to 14. Non-English SharePoint sites require at least version 15.
 

Examples

Get a list of lists in a web:
let
    // Get a list of tables at the site URL... 
    SharePointSiteLists = SharePoint.Tables("https://yourDomain.sharepoint.com/sites/yourSite", [ApiVersion = 15])
in
    SharePointSiteLists

Get details about a list:
let
    // Get a list of tables at the site URL... 
    SharePointSiteLists = SharePoint.Tables("https://yourDomain.sharepoint.com/sites/yourSite", [ApiVersion = 15]),
    // Get a list named "Toys"...
    // (Could also use list GUID - [Id="a1c236c1-7d6d-4141-9d25-bf6f8e25d8a5"] )
    ToyList = SharePointSiteLists{[Title="Toys"]}
in
    ToyList

Get a list of items in a list:
let
    // Get a list of tables at the site URL... 
    SharePointSiteLists = SharePoint.Tables("https://yourDomain.sharepoint.com/sites/yourSite", [ApiVersion = 15]),
    // Get a list named "Toys"...
    // (Could also use list GUID - [Id="a1c236c1-7d6d-4141-9d25-bf6f8e25d8a5"] )
    ToyList = SharePointSiteLists{[Title="Toys"]},
    // Get the items from the list...
    Toys = ToyList[Items]
in
    Toys

 
Here's the output of the three examples above…

The list of lists:


 Data for a list:


List Items: (SharePoint tends to return a lot of extra columns you don't need.)

.

4/29/2019

Three Ways to Load SharePoint Data into Power BI and Excel


Power BI includes a connector/wizard for SharePoint lists that makes is easy to load list content. It works great, but has a shortcoming or two. It first retrieves a list of all of the lists, and then it retrieves all of the list content… even if you only need a small subset of the data.

In this little article I'll show these approaches:

  • Using the Wizards
  • Writing your own "M" formulas
  • Using the SharePoint ODATA RESTful web services, for lists and a lot more!

While they work for Power BI, they also work for Excel Power Query.


Using the Wizards

The wizards will write "M" formulas for you to connect to the SharePoint list. Although this will let you transform the data any way you like, all of the list content will be downloaded into Power Bi and then filtered.

  1. Launch Power BI Desktop
  2. Click Get Data from the ribbon
  3. If the full list of connectors is not displayed, click More.
  4. Scroll, or use the search box, to find and click the “SharePoint Online List” or "SharePoint List" connector.
  5. Enter the Site URL. (Just the URL to the site, not to a page or a list.)
    Example: https://yourDomain.sharepoint.com/sites/yourSite or https://yourDomain.sharepoint.com/sites/yourSite/yourSubSite
  6. Click OK.
  7. Find and checkmark your SharePoint list.
  8. Review your data and click Edit. (SharePoint adds a number of hidden/internal columns that you will probably want to exclude.)
  9. Optional: In the Query Settings area, click in the Name box and enter a new name for the imported data.
  10. Select all of the columns that you do not need and click Remove Columns. Or, select the columns you want to keep, click the dropdown under Remove Columns, and click Remove Other Columns.
  11. Click Close & Apply.
  12. Create your visuals!


Writing your own "M" formulas

The wizards just write formulas for you. With a little practice, you can write better ones! Although this will let you transform the data any way you like, all of the list content will still be downloaded into Power Bi and then filtered. (i.e. keep reading to see how to use the REST web services to download only what you need.)

  1. Launch Power BI Desktop
  2. Click Get Data from the ribbon
  3. Click Blank Query. This will open the Query Editor.
  4. Click the Advanced Editor button.
  5. Write your formula, save your changes.
  6. You can continue customizing the data in the Query Editor.
  7. Click Close & Apply.
  8. Create your visuals!

In the blank query you will see something like this:

let
    Source = ""
in
    Source

When you run the wizard in the previous example, it writes a formula similar to this:

let
    Source = SharePoint.Tables("https://yourDomain.sharepoint.com/sites/yourSite", [ApiVersion = 15]),
    #"a1c236c1-7d6d-4141-9d25-bf6f8e25d8a5" = Source{[Id="a1c236c1-7d6d-4141-9d25-bf6f8e25d8a5"]}[Items],
    #"Renamed Columns" = Table.RenameColumns(#"a1c236c1-7d6d-4141-9d25-bf6f8e25d8a5",{{"ID", "ID.1"}})
in
    #"Renamed Columns"


You can write a better formula than the wizard!

The "let" selects and restructures the data while the "in" is the final result where we get the data for the model. The "let" contains a series of functions that drill down, layer by layer, to get to the data you need.

The "Source" is a variable and can have any name. This first line selects the data source, SQL, SharePoint, etc. While the documentation for SharePoint.Tables says it uses a URL to the list, it is actually the URL to the server. (Kind of like how a SQL connection string names a database, but not a table.)

Here's an example of a hand written "let" statement that uses friendly names instead of GUIDs for tables and obvious names for the variables:

let
    SharePointSite = SharePoint.Tables("https://yourDomain.sharepoint.com/sites/yourSite", [ApiVersion = 15]),
    ToyList = SharePointSite{[Title="Toys"]},
    Toys = ToyList[Items]
in
    Toys

These functions can be chained, so if you prefer a one line version:
let
    Toys = SharePoint.Tables("https://yourDomain.sharepoint.com/sites/yourSite", [ApiVersion = 15]){[Title="Toys"]}[Items]
in
    Toys

And if you want to pick just the columns you need without a lot of clicking…

let
    Toys = SharePoint.Tables("https://yourDomain.sharepoint.com/sites/yourSite", [ApiVersion = 15]){[Title="Toys"]}[Items],
    ToysData = Table.SelectColumns(Toys,{"Id", "Title", "Price", "Modified"})
 in
    ToysData

More on M:
    https://docs.microsoft.com/en-us/powerquery-m/power-query-m-reference

Ok, that's good to know… but we still bring back all of the rows of data, even if we just want a few.


Using the SharePoint ODATA RESTful Web Services

The SharePoint List connector lets you bring in data from SharePoint lists and libraries. Using the SharePoint REST API web services you can bring in just about any kind of data found in SharePoint, including list data, lists of lists, lists of users, lists of sites and much more. Even better, you can write filters and select columns so only the needed data is sent from SharePoint to Power BI. (And with other REST APIs you can bring in just about any data from Office 365!)

  1. Launch Power BI Desktop
  2. Click Get Data from the ribbon
  3. Click OData Feed.
  4. Enter a URL to a SharePoint web service. This one retrieves all Touring bikes.
    This example assumes a list named “Bikes” where we only want three columns and only the rows for "Touring" bikes.
    Example:
    https://yourServer/sites/yourSite/_api/Lists/GetByTitle('Bikes')/Items?$select=price,color,style&$filter=category eq ' Touring'
  5. Click OK.
  6. Enter credentials, if needed.
  7. Click OK.  (You have already selected your columns and rows from the REST query, so no additional Power Query edits needed.)
  8. Create your visuals!

Note: You could start with a Blank Query and enter something like this:

let
    Source = OData.Feed("https://yourDomain.sharepoint.com/sites/yourSite/_api/web/lists/getbytitle('Bikes')/Items?$select=Title,Bike_x0020_Type,Size,Retail", null, [Implementation="2.0"])
in
    Source


There are REST Web Services for Everything!

A few other ideas: (and even more ideas here: https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/get-to-know-the-sharepoint-rest-service)

  • Get a list of all of the lists in a site:
    https://yourServer/sites/yourSite/_api/web/lists
  • Get a list of all items in a site’s recycle bin:
    https://yourServer/sites/yourSite/_api/web/Recyclebin
  • Get a list of all subsites in the current Site Collection:
    https://yourServer/sites/yourSite/_api/web/webs
  • Get a list of all site users:
    https://yourServer/sites/yourSite/_api/web/siteusers?$filter=startswith(LoginName,'i:0%23.f')

You can load multiple REST queries, and then define relationships to build reports for all kinds of SharePoint activity!

 

Learning the SharePoint REST API

To experiment with SharePoint REST services you can add my SharePoint REST Tester to your SharePoint site. See details here:

   https://techtrainingnotes.blogspot.com/2017/04/a-sharepoint-rest-api-tester-with-ajax.html

and download from here:

   https://github.com/microsmith/SharePointRESTtester

10/30/2018

SharePoint Calculated Columns – Convert a Month Name to a Number

Here are three solutions for when you have a column with a three letter abbreviation of a month name, and you want the month number,

Each of the following assume that you have a "Single Line of Text" or "Choice" column named "Month".


A long IF statement:

IF([Month]="JAN",1,
  IF([Month]="FEB",2,
    IF([Month]="MAR",3,
      IF([Month]="APR",4,
        IF([Month]="MAY",5,
          IF([Month]="JUN",6,
            IF([Month]="JUL",7,
              IF([Month]="AUG",8,
                IF([Month]="SEP",9,
                  IF([Month]="OCT",10,
                    IF([Month]="NOV",11,
                      IF([Month]="DEC",12,
                        0
  ))))))))))))


Using the FIND() Function:

=(
   FIND(
           UPPER(D2),
            "   ,JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC")
       ) -1 
 ) / 4

FIND returns the position of the matching text. "JAN" returns five. If we subtract one and then divide the FIND number by four, we get the month number.


Finding the Month Number Using Three or More Month Letters

=(
   FIND(
            UPPER(Month),
            "         ,JANUARY  ,FEBRUARY ,MARCH    ,APRIL    ,MAY      ,JUNE     ,JULY     ,AUGUST   ,SEPTEMBER,OCTOBER  ,NOVEMBER ,DECEMBER "
       )  -1
 ) / 10

This uses the same idea, but the month names are spelled out and are padded with spaces to make each one nine characters long. (Ten with the commas.)

Here’s the result:

MonthNumber



Now available on Amazon!

image

10/15/2018

SharePoint Calculated Columns and Validation Formulas – The Book!


Available on Amazon!


It only took me a year… and it grew from a little booklet to over 200 pages… but its finally done!

Bought the book? Post any questions, bugs, typos and suggestions to this blog post!


Everything you need to know about SharePoint formulas for SharePoint 2010 to 2019 and SharePoint Online / Office 365!

image

This book is for the SharePoint “power user” who needs a better understanding of how SharePoint formulas work, where they don’t work, and how they are created. While at first glance SharePoint formulas appear to be simple calculations, they are both quite powerful and have weird limitations. In this book we will explore the basics of creating Calculated Columns and Validation formulas with some boring details, and over one hundred examples. We also explore workarounds for many of the limitations by using SharePoint Designer workflows and a few tricks!

 

Over 100 Examples!

A how-to book of formulas would not be too useful without a few examples. I've been collecting these for years. They've come from classroom questions, forum questions, and my own SharePoint sites. Now they are all in one place…

  • · Over 60 Calculated Columns examples
  • · Over 30 Column Validation examples
  • · 11 List/Library Item Validation examples
    (and every one of the Column Validation examples can be used here.)
  • · 7 Calculated Column Default Values examples
  • · 15 Workflow “workarounds” for things SharePoint formulas can’t do

Scroll on down for a complete list of the examples.


Who is this book for?

Anyone who creates and customizes lists and libraries. These include: SharePoint on premise Farm Administrators, Office 365 SharePoint administrators, Site Collection Administrators, Site Owners, Power users, and Developers.

 

Where are formulas used in SharePoint?

  • Calculated column formulas derive data from other columns in the same list item. These can be simple calculations and text manipulations, or complex Excel-style formulas.
  • Column validation formulas are used to create custom validations for user entered data. These can be used to make sure that "quantities" are not negative and vacation requests are for dates in the future.
  • Column default formulas, while very limited, can auto-fill columns with a date 30 days in the future or a message based on the month or day of the week the item was added.
  • List / Library validation formulas are used to check the values from multiple columns to verify the relationship between two or more columns. Examples include making sure a task start date is before the task end date, or to make sure an entry has a "price per pound" or a "price each", but not both.


Workflow Workarounds?

SharePoint formulas only work with a few column types while SharePoint workflows can access just about any column type. So, with a little workflow work your formulas can make these columns available to SharePoint formulas. There's over fifty pages of workflow workaround tips:

  • Workaround for People and Groups
  • Getting Data from a Person or Group Column
  • Getting a User’s Manager
  • Workaround for the SUBSTITUTE Function
  • Workaround for Getting View Totals from Calculated Columns.
  • Workaround for Adding Images to a Column
  • Workaround for Multiple Lines of Text
  • Workaround for Lookup Columns
  • Lookup a Single Item (No checkboxes)
  • Extract Several Lookup Columns into a Single Line of Text
  • Workaround for Multivalued Choice Columns
  • Counting Items Selected in a Multivalued Column
  • Workaround for Managed Metadata columns
  • Workaround for Single Valued Managed Metadata Columns
  • Workaround for Multi Valued Managed Metadata Columns
  • Workarounds for Attachments

 

Table of Contents

  • Read Me First
  • Tips for Formulas
  • Calculated Columns
  • Calculated Column Examples
  • Calculated Default Columns
  • Column Validation Formulas
  • Column Validation Examples
  • List/Library Item Validation
  • List/Library Item Validation Examples
  • Other Forms of Validation
  • Workflow Workarounds
  • Error Messages


Examples:

Calculated Column Examples:·

·        Fun stuff that may not work for you:

o   Add HTML to a Calculated Column

o   Add icons or pictures to a Calculated Column

o   Building an Address (String Concatenation)

o   Create a Bar Chart Column

·        Columns for Views

o   View Filtering on a Calculated Column

o   Group by Year

o   Group by Month and Year

o   Group by Year Plus Month

o   Grouping on an Algorithm

·        Numbers

o   Adding Leading Zeros to a Number

o   Scientific Notation

o   Roman Numerals

·        The IF Function and Boolean Logic

o   Calculating a Discount using Nested IFs

o   Working Around Nested IF Limits

o   Convert from State Codes to State Names

o   ANDs and ORs - Approve if all approved, or reject if any one rejects

·        Test for values

o   Testing for a Range of Dates

o   Testing for Empty Columns

o   Testing for Errors

·        Summing and Counting Columns

o   Counting Yes/No Columns

o   Average

o   MIN and MAX

·        More on Numbers

o   Raise a Number to a Power

o   Rounding Numbers

·        Working with Text

o   Combining Text Columns

o   Display First Name and Last Name in a Single Column

o   Creating Title Capitalization

·        Return the Right Data Type!

·        Dates

o   Subtracting Dates

o   Finding a Date One Year in the Future

o   Change Date Formatting

o   Fiscal Year

o   Week Number

o   Day of the Year

o   First Day of the Current Month

o   First Day of the Next Month

o   Last Day of the Current Month

o   Last Day of the Previous Month

o   Last Day of the Next Month

o   Third Tuesday of a Month

o   Skipping Weekend Days

o   Next Workday “X” Days in the Future

o   Simple Solutions for 5, 10, 15 Working Days in the Future

o   Solution for Any Number of Working Days in the Future

o   Working Days

o   Number of Working Hours Between Two Dates

·        Formatting and Conversions

o   Formatting Numbers

o   Adding Text to Numbers

o   Adding Special Symbols (¥, £, etc.) to TEXT() Number Formats

o   Converting Numbers to Fractions

o   Financial Calculations

·        Random Numbers and Messages

o   Creating Random Numbers (Using NOW)

o   Creating Random Messages (using CHOOSE)

·        A Calculated Task Status

·        Great Circle Distance from Longitude and Latitude

·        Simplify a Workflow by Using a Calculated Column

Column Validation Examples

·        Limit time for user updates.

·        Boolean operations

o   Examples Using “OR”

o   Examples Using “AND”

o   Examples Using “AND” and “OR”

o   Examples Using “NOT”

·        Testing for Empty Columns

o   Yes/No

o   Dates

o   ISBLANK()

·        Working with Dates

o   Date Must be in the Future

o   Date Must be in the Future “x” Days

o   Date Must be in the Future “x” Days and not on a Weekend

o   Test Against a Specified Date

o   Date Must be Between Now and the End of the Current Year

o   Date Must be Within the Next 30 days

o   Date Must be the Last Day of the Month

o   Date Must be the First Day of the Month

o   Date Must be the Third Tuesday of the Month

o   Date Can Only be a Weekday

o   Date Can Only be Monday or Wednesday

o   Entered Date Must be for this Year

o   Date Must be Current Or Next Month Only

·        Working with Numbers

o   Testing for Numbers in Custom Increments

o   Limit the Number of Decimal Places Entered

·        Working with Text

o   Testing for Text Length

o   Testing for a Valid State/Province (or other code)

o   Test to Require an Uppercase or Lowercase Letter 

·        Examples Using Pattern Matching

o   Must start with an "a" or "A" and the third character must be a "c" or "C"

o   Match a Phone Number Pattern of xxx-xxx-xxxx

o   Match a Phone Number Pattern of xxx-xxx-xxxx and Limit the Length

o   Match a Phone Number and Make Sure Only Digits Have Been Used


And many more!

Note to spammers!

Spammers, don't waste your time... all posts are moderated. If your comment includes unrelated links, is advertising, or just pure spam, it will never be seen.