Connect to files on OneDrive and Sharepoint

Connect to Excel files using Power Query

Power Query can connect to Excel files held in OneDrive for business and in SharePoint. But it’s not obvious how. Whether you use Excel or PowerBI this approach will work.

Also, for a pure “M” code approach to SharePoint you can use something like this to get your data from a Table in an Excel file-

let

//Set your variables

mySite = “https://aasolutions.sharepoint.com/sites/clientdemos/“,

myFile = “Reconnenct Data Demo.xlsx”,

myTableName = “DataSet”,

//this is then the code….

Source = SharePoint.Files( mySite , [ApiVersion = 15]),

SelectFile = Table.SelectRows(Source, each ([Name] = myFile )),

GetContent = Excel.Workbook(Binary.Combine(SelectFile[Content])),

GetTable = GetContent{[Item=myTableName, Kind=”Table”]}[Data]

GetTable

What is Power Query

Quite simply Microsoft Power Query is an amazing part of Excel and Power BI that everyone should know about.

Plain Speaking: Power Query is the worlds’ greatest washing machine! Get all of your “dirty” data from any location, clean it up via a user friendly interface and then load it all neat and folded to a destination of your choice (Excel or Power Pivot)

In Excel 2010 and Excel 2013 Power Query was a an add-in. In Excel 2016 Power Query was built in to the Data tab in Excel and re-named Get & Transform.

Technical Talk: Power Query is a powerful ETL utility built into Excel 2016 designed to Extract data from multiple sources, Transform the data into a layout suitable for analysis and then Load it into Excel Tables or Power Pivot.