Guy in a Cube – How to expand a list of records in a query

This post will look at two different approaches for expanding a record from a list using Power BI and a bit of M syntax. In our specific case, a customer wanted to pull data from a record that was listed in the table output of a SharePoint list. We're going to focus on the FieldValuesAsText column from that list, but the following would work with any field that was showing Record as the result.

 

 

If you click on Record, you see just a list of the fields for that one record. However, we want the values for every record as a table.

Here is the output for a single row:

Using the UI

From the UI, you can select Drill Down > To Table.

1. Right select the column heading and select Drill Down.

   

2. Right select List in the new table and select To Table.

   

3. In the To Table prompt, for this example, we will leave the delimiter as None.

   

4. Select the column split icon in the Column1 header. Select OK.

   

Here is the syntax that was generated from the above steps. It makes use of the Table.FromList function and then uses the Table.ExpandRecordColumn function.

let
    Source = SharePoint.Tables("https://guyinacube.sharepoint.com", [ApiVersion = 15]),
    #"920f5562-bfc9-4cf0-8c61-402745fe7f06" = Source{[Id="920f5562-bfc9-4cf0-8c61-402745fe7f06"]}[Items],
    FieldValuesAsText = #"920f5562-bfc9-4cf0-8c61-402745fe7f06"[FieldValuesAsText],
    #"Converted to Table" = Table.FromList(FieldValuesAsText, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"ContentTypeId", "Title", "OData__ModerationComments", "File_x0020_Type", "OData__x0069_kc3", "jjbh", "m57j", "ID", "Modified", "Created", "Author", "Editor", "OData__HasCopyDestinations", "OData__CopySource", "owshiddenversion", "WorkflowVersion", "OData__UIVersion", "OData__UIVersionString", "Attachments", "OData__ModerationStatus", "InstanceID", "Order", "GUID", "WorkflowInstanceID", "FileRef", "FileDirRef", "Last_x0020_Modified", "Created_x0020_Date", "FSObjType", "SortBehavior", "FileLeafRef", "UniqueId", "SyncClientId", "ProgId", "ScopeId", "MetaInfo", "OData__Level", "OData__IsCurrentVersion", "ItemChildCount", "FolderChildCount", "Restricted", "OriginatorId", "AppAuthor", "AppEditor", "SMTotalSize", "SMLastModifiedDate", "SMTotalFileStreamSize", "SMTotalFileCount"}, {"Column1.ContentTypeId", "Column1.Title", "Column1.OData__ModerationComments", "Column1.File_x0020_Type", "Column1.OData__x0069_kc3", "Column1.jjbh", "Column1.m57j", "Column1.ID", "Column1.Modified", "Column1.Created", "Column1.Author", "Column1.Editor", "Column1.OData__HasCopyDestinations", "Column1.OData__CopySource", "Column1.owshiddenversion", "Column1.WorkflowVersion", "Column1.OData__UIVersion", "Column1.OData__UIVersionString", "Column1.Attachments", "Column1.OData__ModerationStatus", "Column1.InstanceID", "Column1.Order", "Column1.GUID", "Column1.WorkflowInstanceID", "Column1.FileRef", "Column1.FileDirRef", "Column1.Last_x0020_Modified", "Column1.Created_x0020_Date", "Column1.FSObjType", "Column1.SortBehavior", "Column1.FileLeafRef", "Column1.UniqueId", "Column1.SyncClientId", "Column1.ProgId", "Column1.ScopeId", "Column1.MetaInfo", "Column1.OData__Level", "Column1.OData__IsCurrentVersion", "Column1.ItemChildCount", "Column1.FolderChildCount", "Column1.Restricted", "Column1.OriginatorId", "Column1.AppAuthor", "Column1.AppEditor", "Column1.SMTotalSize", "Column1.SMLastModifiedDate", "Column1.SMTotalFileStreamSize", "Column1.SMTotalFileCount"})
in
    #"Expanded Column1"

Table.FromRecords

If you want to do this with M syntax, you can use the Table.FromRecords function. The FromRecords function takes a set of records and converts it to a table. At that point, you can remove unwanted to columns and continue to mash up your data.

let
    Source = SharePoint.Tables("https://guyinacube.sharepoint.com", [ApiVersion = 15]),
    #"920f5562-bfc9-4cf0-8c61-402745fe7f06" = Source{[Id="920f5562-bfc9-4cf0-8c61-402745fe7f06"]}[Items],
    Records = Table.FromRecords(#"920f5562-bfc9-4cf0-8c61-402745fe7f06"[FieldValuesAsText])
                      ^–This is what converts it from records to a table.
in
    Records

Adam W. Saxton | Microsoft Business Intelligence
@GuyInACube | YouTube | Facebook.com/guyinacube