segunda-feira, abril 11, 2016

Excel Power Query: Concatenating a Multiple-Value List source inside a single cell.

Excel Power Query is an awesome tool to get all kinds of things done .
One can draw in information from anywhere, from Excel tables, csv files, Sharepoint connections or other databases.

Once the information is in the document, one can perform several filters and transformations before the data is actually placed in a table inside the document.

In my case I am working with Sharepoint List sources.

Not all content is displayed verbatim when it comes from Sharepoint. Lists for instance, are simply displayed as [List] inside the Excel's cell.

If your list is a multiple-value one, you may expand it, but you will then multiply the number of rows, which is like a cartesian product.

You may want to present it differently though. Like me, you may want to display the list as a comma-separated value inside the cell.

Turns out Excel Power Query is pretty powerful and there is a way to do that.

A column can be transformed in a lot of ways. This sequence below is buta dab in the water of Power  Query, and may help you people out there.

If yout List is text-based it's a simpler matter:

let

    Source = ...#"your source"...,
    #"transform" = Table.TransformColumns( #"your source" , {"list column", each Text.Combine( _ ,",")})

in
    #"transform"

If your list (column-type) is number-based or a lookup to another Sharepoint List though, it's a bit more involved:

let

    Source = ...#"your source"...,
    #"transform" = Table.TransformColumns( #"your source" , {"list column", each Text.Combine( List.Transform( _ , each Number.ToText(_) ),",")})

in
    #"transform"

So what do we do here?

Table.TransformColumns - This picks up our source column and drill down on each record, performing a transformation function on each.

Text.Combine - this one concatenates a series of text strings from a list.

List.Transform - This performs a transformation for a numeric list (and lookup lists) to become a text list.

Number.ToText - This is applied on each element of the List to make it parsable by Text.Combine.



For the full reference:

Power Query Formula Reference:

https://msdn.microsoft.com/en-us/library/mt211003.aspx