Report on Multiple-Choice Fields in SharePoint Using The Excel add-on Power Query

Today we will be learning how to create a excel report connected to a SharePoint library using Power Query.  I will also address all issues found along the way.

The reason we are connecting to our library via Power Query instead of via the 'Export to Excel' option is because our library contains InfoPath forms that have repeating table data in the columns that we need to report on.

Power Query  
Is a free excel add-in available from Microsoft for both Excel 2010 Professional Plus & Excel 2013.  It is used when you need a way to transform data that isn't quite in the format you need.

Step 1: Connecting to your SharePoint Library via Power Query.
Open up Excel.  Click Power Query Tab > From Other Sources > From SharePoint List > Insert SharePoint Site URL

Here's our first issue.  Only Lists are appearing in the Navigator.  Not to worry, Right-Click any of the Lists showing and click Edit to open up the Query Editor.  You should now see a screen showing the data from the list you selected.  We will now modify the query so you can connect to a SharePoint Library.


Once in the Query Editor, Click Home Tab > Advanced Editor.  

You should see a query something like this:

let

    Source = SharePoint.Tables("http://rootsite/subsite"),

    RandomList = Source{[Name="RandomList"]}[Content]

in

    RandomList

In order to connect to our Library, we need to change the two highlighted sections.  Replace 'Tables' to 'Contents' and replace 'RandomList' to the name of your library without spaces, like this:

let
    Source = SharePoint.Contents("http://rootsite/subsite"),
    RandomList = Source{[Name="InsertLibraryNameHere"]}[Content]
in
    RandomList

If you happen to see the error below, follow these steps to bypass the security measures for connecting to the list data (see Reference link E at the bottom of this post for more information on what this means).

(Formula.Firewall: Query RandomList (RandomList) is accessing data sources that have privacy levels which can not be used together.  Please rebuild this data combination.)

Exit the Query Editor, in Excel: Click Power Query Tab > Workbook Settings > Fast Combine > Select 'Ignore the Privacy levels and potentially improve performance'.

You should now see a list with all the library data!

Step 2: Display Column Data
This is a quick step.   click the Double Arrow next to the 'Attributes' Column, and check which columns you would like to be able to report on.


Step 3: Massage the Data & Create Individual Rows for each cell containing multiple Repeating Table values

This step is the breadwinner.  If you've listed columns that have cells with multiple values in them, then you'll love this (This can occur when you use a multi-choice column in a SharePoint List, or you have data from an InfoPath form with a Repeating Table).

You might notice that rows with multi-value cells only show the first value:

But once you click on the cell, you will see all the values inside that cell.  In my instance, it's listing multiple types of software:

Here's a list of the issues we need to solve here:

• Problem #1:  we need a common delimiter to split the cell values by.  There's no way to split Carraige Returns (new lines/enter/etc) in the GUI.
• Problem #2:  We need to turn the column holding multiple values into a List/Table so that we can attack problem #3
• Problem #3:  In order to report on every individual value (piece of software), we need to insert each value into a new row.

Solving Problem #1:  Here we are going to convert our carriage returns into commas:

• Right click the Column in question > Click Replace Values.  
• In Value to Find, insert a the word 'REPLACE'.
• In Replace With, insert a comma & click OK
• Nothing should have happened.  Now go back to the Advanced Editor and find the word 'REPLACE'.  Replace that word with the the following: #(lf)
• Your code should look something like this:

let

    //CONNECT TO SHAREPOINT LIBRARY

    Source = SharePoint.Contents("http://rootsite/subsite"),
    RandomList = Source{[Name="InsertLibraryNameHere"]}[Content]

    //SELECT THE COLUMNS TO REPORT ON

    #"Expand Attributes" = Table.ExpandRecordColumn(#"SelectBaseColumns", "Attributes", {"ColumnA", "ColumnB"}, {"ColumnA", "ColumnB"}),

    //REMOVE CARRIAGE RETURNS AND REPLACE WITH COMMAS

    #"Replaced Value" = Table.ReplaceValue(#"Expand Attributes","#(lf)",",",Replacer.ReplaceText,{"ColumnA"}),

in
    #"Replaced Value"

Solving Problem #2:  Here we are going to turn ColumnA into a List so we can attack Problem #3.  This also needs to be done via Advanced Editor.  Insert the Highlighted code below into your query:

let

    //CONNECT TO SHAREPOINT LIBRARY

    Source = SharePoint.Contents("http://rootsite/subsite"),
    RandomList = Source{[Name="InsertLibraryNameHere"]}[Content]

    //SELECT THE COLUMNS TO REPORT ON

    #"Expand Attributes" = Table.ExpandRecordColumn(#"SelectBaseColumns", "Attributes", {"ColumnA", "ColumnB"}, {"ColumnA", "ColumnB"}),

    //REMOVE CARRIAGE RETURN AND REPLACE WITH COMMA

    #"Replaced Value" = Table.ReplaceValue(#"Expand Attributes","#(lf)",",",Replacer.ReplaceText,{"ColumnA"}),

    //TURN MULTI-VALUE FIELD INTO A LIST
    ToList = Table.TransformColumns(#"Replaced Value",{{"ColumnA", Splitter.SplitTextByDelimiter(",")}})
in
    ToList

Solving Problem #3: Transform Data from your List Column into one row per value.  I did this through the Advanced Editor as well.  Insert the Highlighted code below into your query:

let
    //CONNECT TO SHAREPOINT LIBRARY
    Source = SharePoint.Contents("http://rootsite/subsite"),
    RandomList = Source{[Name="InsertLibraryNameHere"]}[Content]

    //SELECT THE COLUMNS TO REPORT ON
    #"Expand Attributes" = Table.ExpandRecordColumn(#"SelectBaseColumns", "Attributes", {"ColumnA", "ColumnB"},       {"ColumnA", "ColumnB"}),

    //REMOVE CARRIAGE RETURN AND REPLACE WITH COMMA
    #"Replaced Value" = Table.ReplaceValue(#"Expand Attributes","#(lf)",",",Replacer.ReplaceText,{"ColumnA"}),

    //TURN MULTI-VALUE FIELD INTO A LIST
    ToList = Table.TransformColumns(#"Replaced Value",{{"ColumnA", Splitter.SplitTextByDelimiter(",")}}),

    //CREATE A TABLE COLUMN THAT CONTAINS YOUR LIST COLUMN
    #"AddTempColumn" = Table.AddColumn(ToList, "TempTableColumn", each Table.FromColumns({[ColumnA]})),

    //EXPAND TEMP TABLE COLUMN INTO ONE ROW PER VALUE
    #"AddAllRows" = Table.ExpandTableColumn(#"AddTempColumn", "TempTableColumn", {"Column1"}, {"ColumnB"})
in
    AddAllRows

Step 4: Clean up Columns and Load Data to Excel
Now that we've successfully massaged the data and got it presented how we want, now it's time to load it to excel.

Before loading it to excel, Remove all Binary, List & Table Columns from the data.  This is because once it's pushed to Excel, excel can sometimes refuse to load when these columns are still displayed.

Once the columns are removed, Click Home Tab > Close & Load to push to excel.

We are done here.

Step X:  Additional Useful Information.
My list actually had MULTIPLE columns with multi-value data.  So here's the final code I used to create all the columns as List Columns, then combine them into the Table Column together, then Expand them all together (Because the repeating table data was all related to each other).  You may notice I skipped the 'Change to Comma' step.:

let
    //CONNECT TO SHAREPOINT LIBRARY
    Source = SharePoint.Contents("http://rootsite/subsite"),
    RandomList = Source{[Name="InsertLibraryNameHere"]}[Content]

    //SELECT THE COLUMNS TO REPORT ON
    #"Expand Attributes" = Table.ExpandRecordColumn(#"SelectBaseColumns", "Attributes", {"ColumnA", "ColumnB"},       {"ColumnA", "ColumnB"}),
    //TURN ALL MULTI-VALUE FIELDS INTO LISTS
    ToList = Table.TransformColumns(#"Formatting-Version",{
        {"ColumnA", Splitter.SplitTextByDelimiter("#(lf)")},
        {"ColumnB", Splitter.SplitTextByDelimiter("#(lf)")},
        {"ColumnC", Splitter.SplitTextByDelimiter("#(lf)")},
        {"ColumnD", Splitter.SplitTextByDelimiter("#(lf)")},
        {"ColumnE", Splitter.SplitTextByDelimiter("#(lf)")}
    }),

    //ADD ALL LIST COLUMNS TO TABLE COLUMN.  THEN EXPAND TABLE COLUMN INTO INDIVIDUAL ROWS FOR ALL THE DATA
    #"AddTempColumn" = Table.AddColumn(ToList, "Temp_ListColumn", each Table.FromColumns({[ColumnA],[ColumnB],[ColumnC],[ColumnD],[ColumnE]})),
    #"AddAllRows" = Table.ExpandTableColumn(#"AddTempColumn", "Temp_ListColumn", {"Column1", "Column2", "Column3", "Column4", "Column5"}, {"ColumnAA", "ColumnBB", "ColumnCC", "ColumnDD", "ColumnEE"}),

    //REMOVE ALL UNNECCESSARY COLUMNS
    #"Remove Columns" = Table.RemoveColumns(#"AddAllRows",{"ColumnA", "ColumnB", "ColumnC", "ColumnD", "ColumnE"})

//DISPLAY THE CONTENT
in

   #"Remove Columns"


REFERENCES (and thanks!):

A) https://social.technet.microsoft.com/Forums/en-US/c5e2e2dd-98e7-40f7-aa1f-f201f5e98059/create-a-new-table-using-repeating-rows?forum=powerquery

B) https://support.office.com/en-sg/article/Power-Query-formula-categories-125024ec-873c-47b9-bdfd-b437f8716819

C) https://support.office.com/en-sg/article/Power-Query-formula-categories-125024ec-873c-47b9-bdfd-b437f8716819

D) https://social.technet.microsoft.com/Forums/en-US/bf633836-b8cb-412c-a02b-c1199ace84d9/power-query-expand-multiple-lists-within-a-row-at-once?forum=powerquery

E) https://support.office.com/en-us/article/Privacy-levels-Power-Query--cc3ede4d-359e-4b28-bc72-9bee7900b540?ui=en-US&rs=en-US&ad=US