Table of contents
TOC
Collapse the table of content
Expand the table of content

Accessing data functions

Owen Duncan|Last Updated: 5/22/2018

Accessing data

Functions in this section access data and return table values. Most of these functions return a table value that is called a navigation table. A navigation table is a two column table. The first column contains the name of an item and the corresponding second column contains the value of that item. This shape is primarily used by the Power Query user interface to provide navigation experience over the potentially large hierarchical data returned.

FunctionDescription
Access.DatabaseReturns a structural representation of an Microsoft Access database.
ActiveDirectory.DomainsReturns a table with Domain information available in the current domain or optional Active Directory forest.
AdobeAnalytics.CubesReturns a table of multidimensional packages from Adobe Analyics.
AdoDotNet.DataSourceReturns the schema collection for an ADO.NET data source.
AdoDotNet.QueryReturns the result of running a native query on an ADO.NET data source.
AnalysisServices.DatabaseReturns a table of multidimensional cubes or tabular models from the Analysis Services database.
AnalysisServices.DatabasesReturns the Analysis Services databases on a particular host.
AzureStorage.BlobContentsReturns the content of the specified blob from an Azure storage vault.
AzureStorage.BlobsReturns a navigational table containing all containers found in the Azure Storage account. Each row has the container name and a link to the container blobs.
AzureStorage.TablesReturns a navigational table containing a row for each table found at the account URL from an Azure storage vault. Each row contains a link to the azure table.
Csv.DocumentReturns the contents of a CSV document as a table using the specified encoding.
CsvStyle.QuoteAfterDelimiterQuotes in a field are only significant immediately following the delimiter.
CsvStyle.QuoteAlwaysQuotes in a field are always significant regardless of where they appear.
Cube.AddAndExpandDimensionColumnMerges the specified dimension table, dimensionSelector, into the cube’s, cube, filter context and changes the dimensional granularity by expanding the specified set, attributeNames, of dimension attributes.
Cube.AddMeasureColumnAdds a column with the name column to the cube that contains the results of the measure measureSelector applied in the row context of each row.
Cube.ApplyParameterReturns a cube after applying parameter with arguments to cube.
Cube.AttributeMemberIdReturns the unique member identifier from a member property value.
Cube.AttributeMemberPropertyReturns the property propertyName of dimension attribute attribute.
Cube.CollapseAndRemoveColumnsChanges the dimensional granularity of the filter context for the cube by collapsing the attributes mapped to the specified columns columnNames.
Cube.DimensionsReturns a table containing the set of available dimensions within the cube.
Cube.DisplayFoldersReturns a nested tree of tables representing the display folder hierarchy of the objects (e.g. dimensions and measures) available for use in the cube.
Cube.MeasuresReturns a table containing the set of available measures within the cube.
Cube.ParametersReturns a table containing the set of parameters that can be applied to cube.
Cube.PropertiesReturns a table containing the set of available properties for dimensions that are expanded in the cube.
Cube.PropertyKeyReturns the key of property property.
Cube.ReplaceDimensions
Cube.TransformApplies the list cube functions, transforms, on the cube.
DB2.DatabaseReturns a table with data relating to the tables in the specified DB2 Database.
Excel.CurrentWorkbookReturns the tables in the current Excel workbook
Excel.WorkbookReturns a table representing sheets in the given excel workbook.
Exchange.ContentsReturns a table of contents from a Microsoft Exchange account.
Facebook.GraphReturns a table containing content from the Facebook graph .
File.ContentsReturns the binary contents of the file located at a path.
Folder.ContentsReturns a table containing the properties and contents of the files and folders found at path.
Folder.FilesReturns a table containing a row for each file found at a folder path, and subfolders. Each row contains properties of the folder or file and a link to its content.
GoogleAnalytics.AccountsReturns the Google Analytics accounts for the current credential.
Hdfs.ContentsReturns a table containing a row for each folder and file found at the folder url, {0}, from a Hadoop file system. Each row contains properties of the folder or file and a link to its content.
Hdfs.FilesReturns a table containing a row for each file found at the folder url, {0}, and subfolders from a Hadoop file system. Each row contains properties of the file and a link to its content.
HdInsight.ContainersReturns a navigational table containing all containers found in the HDInsight account. Each row has the container name and table containing its files.
HdInsight.ContentsReturns a navigational table containing all containers found in the HDInsight account. Each row has the container name and table containing its files.
HdInsight.FilesReturns a table containing a row for each folder and file found at the container URL, and subfolders from an HDInsight account. Each row contains properties of the file/folder and a link to its content.
Informix.DatabaseReturns a table of SQL tables and views available in an Informix database on server server in the database instance named database.
Json.DocumentReturns the contents of a JSON document. The contents may be directly passed to the function as text, or it may be the binary value returned by a function like File.Contents.
Json.FromValueProduces a JSON representation of a given value value with a text encoding specified by encoding.
MySQL.DatabaseReturns a table with data relating to the tables in the specified MySQL Database.
OData.FeedReturns a table of OData feeds offered by an OData serviceUri.
Odbc.DataSourceReturns a table of SQL tables and views from the ODBC data source specified by the connection string connectionString.
Odbc.InferOptionsReturns the result of trying to infer SQL capabilities for an ODBC driver.
Odbc.QueryConnects to a generic provider with the given connection string and returns the result of evaluating the query.
OleDb.DataSourceReturns a table of SQL tables and views from the OLE DB data source specified by the connection string.
OleDb.QueryReturns the result of running a native query on an OLE DB data source.
Oracle.DatabaseReturns a table with data relating to the tables in the specified Oracle Database.
PostgreSQL.DatabaseReturns a table with data relating to the tables in the specified PostgreSQL Database.
RData.FromBinaryReturns a record of data frames from the RData file.
Salesforce.DataConnects to the Salesforce Objects API and returns the set of available objects (i.e. Accounts).
Salesforce.ReportsConnects to the Salesforce Reports API and returns the set of available reports.
SapBusinessObjects.UniversesConnects to the SAP BusinessObjects BI Universe at the specified URL and returns the set of available universes.
SapBusinessWarehouse.CubesReturns the InfoCubes and queries in an SAP Business Warehouse system grouped by InfoArea.
SapBusinessWarehouseExecutionMode.DataStream'DataStream flattening mode' option for MDX execution in SAP Business Warehouse.
SapBusinessWarehouseExecutionMode.BasXml'bXML flattening mode' option for MDX execution in SAP Business Warehouse.
SapBusinessWarehouseExecutionMode.BasXmlGzip'Gzip compressed bXML flattening mode' option for MDX execution in SAP Business Warehouse. Recommended for low latency or high volume queries.
SapHana.DatabaseReturns the packages in an SAP HANA database.
SapHanaDistribution.AllReturns the packages in an SAP HANA database.
SapHanaDistribution.Connection'Connection' distribution option for SAP HANA.
SapHanaDistribution.Off'Off' distribution option for SAP HANA.
SapHanaDistribution.Statement'Statement' distribution option for SAP HANA.
SapHanaRangeOperator.Equals'Equals' range operator for SAP HANA input parameters.
SapHanaRangeOperator.GreaterThan'Greater than' range operator for SAP HANA input parameters.
SapHanaRangeOperator.GreaterThanOrEquals'Greater than or equals' range operator for SAP HANA input parameters.
SapHanaRangeOperator.LessThan'Less than' range operator for SAP HANA input parameters.
SapHanaRangeOperator.LessThanOrEquals'Less than or equals' range operator for SAP HANA input parameters.
SapHanaRangeOperator.NotEquals'Not equals' range operator for SAP HANA input parameters.
SharePoint.ContentsReturns a table containing a row for each folder and document found at the SharePoint site url. Each row contains properties of the folder or file and a link to its content.
SharePoint.FilesReturns a table containing a row for each document found at the SharePoint site url, and subfolders. Each row contains properties of the folder or file and a link to its content.
SharePoint.TablesReturns a table containing the result of a SharePoint List as an OData feed.
Soda.FeedReturns the resulting table of a CSV file that can be accessed using the SODA 2.0 API. The URL must point to a valid SODA-compliant source that ends in a .csv extension.
Sql.DatabaseReturns a table containing SQL tables located on a SQL Server instance database.
Sql.DatabasesReturns a table with references to databases located on a SQL Server instance. Returns a navigation table.
Sybase.DatabaseReturns a table with data relating to the tables in the specified Sybase Database.
Teradata.DatabaseReturns a table with data relating to the tables in the specified Teradata Database.
WebAction.RequestCreates an action that, when executed, will return the results of performing a method request against url using HTTP as a binary value.
Web.ContentsReturns the contents downloaded from a web url as a binary value.
Web.PageReturns the contents of an HTML webpage as a table.
WebMethod.DeleteSpecifies the DELETE method for HTTP.
WebMethod.GetSpecifies the GET method for HTTP.
WebMethod.HeadSpecifies the HEAD method for HTTP.
WebMethod.PatchSpecifies the PATCH method for HTTP.
WebMethod.PostSpecifies the POST method for HTTP.
WebMethod.PutSpecifies the PUT method for HTTP.
Xml.DocumentReturns the contents of an XML document as a hierarchical table (list of records).
Xml.TablesReturns the contents of an XML document as a nested collection of flattened tables.
© 2018 Microsoft