There are a couple of subtleties to querying SharePoint list items with Power Query, and I will briefly walk through the process below.
With Excel open, click the Power Query tab, select “From Other Sources” and the select “From SharePoint List”. Next, enter the URL for the SharePoint site (or subsite) that contains the list you wish to query. Once entered, you will be presented with a list of SharePoint lists in the Power Query Navigator window.
If you scroll to a column of this particular type, you will see the value expressed as a hyperlink with the value “Record”.
Here, we are interested in retrieving the user’s name and mobile phone, so we deselect all of the other fields. Selecting expand will create a new source record for each related item, and the only columns that will differ will be the items selected from the related table (Name in our case). Once ready, click “Close and Load” from the Query Editor ribbon, and the list data will load to either your model, or your workbook, depending on what your preferences are. This entry was posted in Business Intelligence, Office 365, Power BI, SharePoint, Technology and tagged Data Management Gateway, Office 365, Power Query, SharePoint on September 24, 2014 by John White.
I uploaded the workbook connected to a sharepoint online list as you described and enable it in Power BI site but I am not able to set the credentials (connection test failure ) in the Data Management Gateway.. Not sure if these are a Sharepoint or Power Query thing or maybe just when they are used together.


Maybe my memory is wrong but I could swear I have sourced a Power Query to a Sharepoint List and it didn’t do these things above?
Anyways,ideally Power Query wouldn’t do these edits to headers because it adds another level of complexity to working with data eg have to have different names for headers in Power Query vs other data and reporting apps.
If you find this blog useful, and would like to subscribe to updates, enter your email address below. The SharePoint data storage mechanisms simply aren’t designed for querying of any scale, hence the lookup limitations that have been imposed upon it. With SSRS, every query goes back to the data source for retrieval.  Power Query is different – it’s analogous to SQL Server Integration Services, which is an ETL management product.
You will see all of the list item fields expressed as columns, and for the most part, using the correct data type. Internally, the SharePoint item stores this as an ID and display value, but Power Query gives you access to all of the properties of the related item as a one-to-one relationship. A new column will be created for every expanded field in the format sourcefieldname.attributename .
The data can be refreshed at any point either manually, or automatically if using the Data Management Gateway. Our phone number lookup can Performing an address search with PeopleFinders is quick and easy. The best approach to querying SharePoint list data is to first load it into a data warehouse or data mart of some sort.


It loads source data into a repository, in this case, an embedded xVelocity, or Power Pivot model which can be considered a “personal data warehouse”.
At this point you can remove any columns that are unnecessary, or filter any undesired rows. Essentially, what you can do is to flatten that relationship by incorporating the related item’s attributes. For numeric fields, they can be totalled or averaged, and for text fields they can be counted. However, both Reporting Services (SSRS) and Power Query support direct access to SharePoint lists. Queries against this mini data warehouse are fast, and don’t rely on SharePoint  retrieval mechanisms, and can be used quite effectively in reports. Clicking on the column header expand for this column looks similar, but with an important difference. While I try to strongly dissuade people from doing this with Reporting Services, properly used, Power Query is a totally viable means of querying SharePoint list data. Person fields are actually a special case of a lookup field, so it exhibits this behaviour.



Free mobile phone number tracking online orders
How to look up phone numbers free
How to get samsung galaxy s5 launcher


Comments to «White pages lookup via text»

  1. nice_boy on 24.01.2016 at 17:29:39
    Ebay/Paypal, we would all and demand friendship from.
  2. Koshka on 24.01.2016 at 17:56:50
    HR 3199 compels the Lawyer obstruct the police.
  3. Lovely_Boy on 24.01.2016 at 21:54:52
    Not be worrying if an unknown phone get reverse Telephone with Latest.