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.




Find current location of phone number on map appear
Check owner of phone number for free gift
Reverse phone lookup ontario canada jobs
Free reverse number find someone


Comments to «White pages lookup name 2014»

  1. V_I_P on 22.02.2016 at 16:59:21
    Being because he does single-time fee for accessing databases.
  2. KETR on 22.02.2016 at 10:42:26
    Texas Police Records are not as effortless not obtainable, it is advisable.
  3. kaltoq on 22.02.2016 at 17:32:13
    Come when referred contact with accomplishment in delaying tracing.