Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Returning The Contents Of Any Table In Any Excel Workbook In Sharepoint With Power Pivot And Power Query

with 11 comments

Yet another Power Query post this week (with more to come) – today I’m going to show you how you can use Power Pivot and Power Query to build a dynamic solution that allows a user to browse through all the Excel workbooks stored in a Sharepoint document library and see the contents of any table in any workbook they choose. Why would you want to do this? Well, I’m not really sure, but I visited a customer a few months ago where an analyst (a data steward?!) had created a repository of Excel workbooks containing various datasets used for BI in Sharepoint. Maybe it could be useful to use Power Query to browse these datasets… and even if it isn’t, it’s still a learning experience for me :-)

The first step is to build a Power Query query that returns a list of all of the Excel workbooks in a Sharepoint document library. There are a couple of ways of doing this; you could use the SharePoint.Contents() function, but I opted to use the OData REST api that Sharepoint exposes because it was faster and easier to work with. The following Power Query query is all that’s needed to get a list of files in a library called MetadataTest:

let

    Source = OData.Feed("https://zzzz.sharepoint.com/_vti_bin/listdata.svc/MetadataTest"),

    RemovedOtherColumns = Table.SelectColumns(Source,{"Id", "ContentType", "Path", "Name"})

in

    RemovedOtherColumns

 

image

These all happen to be Excel workbooks, which makes things easier for me.

The next thing to do is to get a list of all the tables in any of these Excel workbooks. Again, you can use OData for this. Here’s an example query that requests the $metadata of the OData endpoint that an Excel workbook called FruitVegPeople.xlsx exposes, interprets the response as an XML document, and finds the list of tables in the workbook from that XML document (this last step accounts for 90% of the code below):

let

    Source = Xml.Document(

    OData.Feed(

    "https://zzzz.sharepoint.com/

    _vti_bin/ExcelRest.aspx/MetadataTest/FruitVegPeople.xlsx/OData/$metadata"

    )),

    Value = Source{0}[Value],

    Value1 = Value{0}[Value],

    Value2 = Value1{0}[Value],

    Value3 = Value2{3}[Value],

    #"Expand Value" = Table.ExpandTableColumn(Value3, "Value"

        , {"Name", "Namespace", "Value", "Attributes"}

        , {"Value.Name", "Value.Namespace", "Value.Value", "Value.Attributes"}),

    #"Expand Attributes" = Table.ExpandTableColumn(#"Expand Value", "Attributes"

        , {"Name", "Namespace", "Value"}

        , {"Attributes.Name", "Attributes.Namespace", "Attributes.Value"}),

    FilteredRows = Table.SelectRows(#"Expand Attributes", each ([Attributes.Name] = "Name")),

    RemovedOtherColumns = Table.SelectColumns(FilteredRows,{"Attributes.Value"}),

    RenamedColumns = Table.RenameColumns(RemovedOtherColumns,{{"Attributes.Value", "TableName"}})

in

    RenamedColumns

 

In this case, there are three tables called Fruit, Vegetable and Person returned:

image

At this point you can find a list of files in a library and find a list of tables in an Excel workbook; what you really need is a single list of all the tables in all of the files. This can be accomplished by turning the second query above into a function, and calling that function for each file returned by the first query. Here’s the function:

let

    Source = (SharepointRootURL, ExcelSourceDoc) => 

 

let

    Source = 

    Xml.Document(

    OData.Feed(SharepointRootURL & "/_vti_bin/ExcelRest.aspx" & ExcelSourceDoc & "/OData/$metadata"

    )

    ),

    Value = Source{0}[Value],

    Value1 = Value{0}[Value],

    Value2 = Value1{0}[Value],

    Value3 = Value2{3}[Value],

    #"Expand Value" = Table.ExpandTableColumn(Value3, "Value", {"Name", "Namespace"

    , "Value", "Attributes"}, {"Value.Name", "Value.Namespace", "Value.Value", "Value.Attributes"}),

    #"Expand Attributes" = Table.ExpandTableColumn(#"Expand Value", "Attributes"

    , {"Name", "Namespace", "Value"}, {"Attributes.Name", "Attributes.Namespace", "Attributes.Value"}),

    FilteredRows = Table.SelectRows(#"Expand Attributes", each ([Attributes.Name] = "Name")),

    RemovedOtherColumns = Table.SelectColumns(FilteredRows,{"Attributes.Value"}),

    RenamedColumns = Table.RenameColumns(RemovedOtherColumns,{{"Attributes.Value", "TableName"}})

in

    RenamedColumns

 

in

    Source

 

And here it is used:

let

    SharepointRootURL = "https://zzzz.sharepoint.com",

    Source = OData.Feed(SharepointRootURL & "/_vti_bin/listdata.svc/MetadataTest"),

    RemovedOtherColumns = Table.SelectColumns(Source,{"Id", "ContentType", "Path", "Name"}),

    InsertedCustom = Table.AddColumn(RemovedOtherColumns, "Custom"

    , each GetTablesFunction(SharepointRootURL, [Path] & "/" & [Name] )),

    #"Expand Custom" = Table.ExpandTableColumn(InsertedCustom, "Custom", {"TableName"}, {"TableName"}),

    InsertedCustom1 = Table.AddColumn(#"Expand Custom", "TableODataURL", each SharepointRootURL 

    & "/_vti_bin/ExcelRest.aspx" & [Path] & "/"  & [Name] & "/OData/" & [TableName]),

    RemovedColumns = Table.RemoveColumns(InsertedCustom1,{"ContentType", "Id"})

in

    RemovedColumns

 

image

This gives you the list of workbooks and tables you need, and this table itself can now be loaded into the Excel Data Model. You can now build a report using this data something like this:

image

The slicers allow the user to select a folder, a workbook and a table in a workbook. In the top right hand corner is a PivotTable displaying a measure with the following definition:

TableODataURLMeasure:=

IF(HASONEVALUE(GetDocumentsTables[TableODataURL]), VALUES(GetDocumentsTables[TableODataURL]))

This returns the URL containing the OData feed for the contents of the selected table. The PivotTable is a bit of a red herring, in fact – I only created it so the slicer highlighting would work properly, a trick I use quite frequently when I’m working with Excel cube functions (the PivotTable itself can be hidden from the end user). In the bottom right hand corner, in the cell selected in the screenshot above, is a CubeValue() function that returns the same measure value and I’ve put that cell into an Excel table – which means that we can now use this measure value as an input to yet another Power Query query.

Two last bits of code: first, a Power Query function that will return the contents of any OData feed passed to it.

let

    Source = (ODataTableURL) => 

 

let

    Source = OData.Feed(ODataTableURL),

    RemovedColumns = Table.RemoveColumns(Source,{"excelUpdated", "excelRowID"})

in

    RemovedColumns

 

in

    Source

 

Finally, a query that takes this function and calls it for the URL selected by the user above:

let

    Source = GetTableContentsFunction(Excel.CurrentWorkbook(){[Name="TableODataURL"]}[Content]{0}[URL])

in

    Source

 

And there you have it – a way of selecting any table in any Excel workbook in a Sharepoint library and seeing its contents. You can download the sample workbook here, although of course you’ll have to modify all the URLs to make it work on. 

PS Is it just me, or does “Power Query query” seem like a bit of a mouthful? Would it be ok just to talk about queries in future? That seems a bit misleading… maybe I should talk about “a Power Query”? That doesn’t sound right either. Hmmm.

Written by Chris Webb

August 29, 2013 at 11:28 pm

Posted in Power Query

11 Responses

Subscribe to comments with RSS.

  1. Maybe “PQ query”?

    George Qiao

    August 30, 2013 at 7:26 am

    • I am confronted with are very similar requirement which requires consolidating several sheets in several workbooks stored in a SharePoint library. Unfortunately, it is a SharePoint 2007 site, and the Odata service does not seem to be available in this version. I had to use the “export to spreadsheet” feature of the library to get the list of files.
      But a very instructive post anyway. Thanks.

      Bertrand

      September 2, 2013 at 10:29 am

  2. […] running sum. In this case, I decided to use a function (see here for some background on this, and here for a more complex example of a function) to do the calculation. Here’s the code of the function, […]

  3. […] running sum. In this case, I decided to use a function (see here for some background on this, and here for a more complex example of a function) to do the calculation. Here’s the code of the function, […]

  4. Hi, Chris. Is it possible to return an Excel Table from another workbook that is not on Sharepoint? Obviously you can return a Table from the current workbook (Excel.CurrentWorkbook) and you can return sheets from another workbook (Excel.Workbook), which you can then whittle down to a table that matches your original table. And now I see that it can be done through odata with Sharepoint. But it’s not intuitive how to do it with a standalone, outside workbook.

    Thanks for all the great work you’re doing with Power Query.

    Jim

    February 6, 2014 at 4:19 pm

    • No, I don’t think it is possible to do this – it’s a limitation of the driver used by PQ rather than PQ itself.

      Chris Webb

      February 6, 2014 at 4:59 pm

  5. Hi Chris, really helpfull post, thanks! I’ve bumped into a similar problem, and your work helped me a lot to create my solution. I’m no expert in programming and naturally my case had its own nuances, but I have managed to overcome them so far. However, I’m now stuck in a complication I’m not finding any clue on the internet to solve it and I would be really gratefull if you could shed some light. The line Value3 = Value2{3}[Value], in the GetTablesFunction is only applicable if there are always 3 tables in the workbooks one is looping through. In my case that number can vary. Most of the excel files I am dealing with have just one table, but there are a few that have more. I’m wondering if there’s any property of Value2 that retrieves the # of elements of that array, therefore making it possible to write something like len = Value2.Length, Value3 = Value2{len}[Value]. Note: each file has only 1 table/sheet, so those that have 3, have also 3 sheets. I’m not sure if this is relevant, but I think it’s worth mentioning. Thank you (and sorry for the long comment)

    João

    September 15, 2014 at 7:06 pm

    • Hi Joao, the Value3 line isn’t referring to the number of tables in the workbook – it’s referring to the part of the XML in the response from the web service that contains the list of tables.

      Chris Webb

      September 15, 2014 at 9:51 pm

      • Chris, thank you very much for the quick response. Ok I get that. However, the issue is that if I write Value2{3}[Value], the query breaks and shows the error: “There were not enough elements in the enumeration to complete the operation.”. If I write Value2{1}[Value] then the query puts the actual table names in the TableName column for workbooks with a single table (hence 1 sheet), but for workbooks with 3 tables or 5 tables (1/sheet), that same TableName column contains the column names of the tables (notice it does not specify from which table those columns are, the tables have all the same column names. I’ve tested to change a column name and it just alters in the query when I’ve changed in the majority of the tables…curious). So, for single sheet single table wb, number 1 refers to the part of the XML that contains the list of tables, but I’m guessing that for a wbs with >1 tables that number refers to the part of the XML that contains the column names… is there any property that retrieves the proper index for the table names for all situations?

        João

        September 16, 2014 at 10:37 am

      • Ah ok, I understand you now. Unfortunately I don’t know the answer – I’ll take a look in a few days when I have some free time.

        Chris Webb

        September 16, 2014 at 10:13 pm

      • Ok thanks for taking time to think about this!

        João

        September 18, 2014 at 9:40 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,202 other followers

%d bloggers like this: