Create custom folding function for STRING_AGG in PostgreSQL

[Alex_RM]

Hello,

I am working for a while with a PostgreSQL database. 

The query folding works well on most of the Power Query steps I added (such as adding simple columns, filter rows, and so on), but one very important function to me isn't folded: STRING_AGG, see PostgreSQL STRING_AGG() Function By Practical Examples (neon.tech). It's an aggregation function working along with GROUP BY statement (the same way as SUM for example).

The equivalent of STRING_AGG in Power Query would be Text.Combine, but this function appears not to be foldable. Groupping a table with a numeric aggregation function (List.Sum, List.Count, ...) is foldable, while a text aggregation with Text.Combine isn't.

For example, this PowerQuery statement is folded:

Table.Group(previousStep, {"KeyColumn"}, {{"AggColumn", each List.Sum([IntColumn]), type number}})

while this one isn't:

Table.Group(previousStep, {"KeyColumn"}, {{"AggColumn", each Text.Combine([TextColumn], ", "), type text}})

So, is it possible to add a custom translator PowerQuery -> SQL for the Text.Combine function, which will be automatically called each time the PowerQuery folding engine reads a Text.Combine function? Or, do you know if there is a foldable workaround to avoid usage of Text.Combine while achieving the same behavior?

I read some posts about the Value.NativeQuery function, but it's not convenient at all, since I need to manually write the SQL statement.

[DataNinja777]

Hi @Alex_RM ,

One way to approach your issue is to create a SQL view in your PostgreSQL database that uses the STRING_AGG function and then connect Power Query to the view.

Define the view in your database:

sql

CREATE VIEW AggregatedData AS
SELECT KeyColumn, STRING_AGG(TextColumn, ', ') AS AggColumn
FROM your_table
GROUP BY KeyColumn;

 In Power Query, connect to the view:

let
    Source = PostgreSQL.Database("your_connection_string"),
    AggregatedData = Source{[Schema="public", Item="AggregatedData"]}[Data]
in
    AggregatedData

• Simplifies the Power Query logic.
• Folding is fully supported because the heavy lifting is done in SQL.

Best regards,

[Alex_RM]

Hi @DataNinja777 , thank you for your quick reply.

However, as I have only read access to the database (and admins made me understand that no changes were possible), I can't process as you propose. 

Also, be able to create custom folding logic between PowerQuery and SQL might even more accelerate data exploration in PowerBI, and thus be useful to a wide range of users.

[v-yiruan-msft · Accepted solution]

Hi @Alex_RM ,

There are two suggested methods to address this issue:

1. Write the SQL Query in Power Query: Use Value.NativeQuery to directly write the SQL query that includes the STRING_AGG function. This ensures that the aggregation is performed on the SQL Server side, maintaining query folding.

let
    Source = Sql.Database("Servername", "Database name"),
    Query = "
        SELECT KeyColumn, STRING_AGG(TextColumn, ', ') AS AggColumn
        FROM YourTable
        GROUP BY KeyColumn
    ",
    Result = Value.NativeQuery(Source, Query)
in
    Result

2. Use DAX's CONCATENATEX Function: Perform the aggregation using DAX in Power BI. This method involves creating a new calculated column or measure using the CONCATENATEX function to achieve the same result.

ConcatenateX in Power BI and DAX: Concatenate Values of a Column - RADACAD

Best Regards

[Alex_RM]

Hello @v-yiruan-msft , thank you for your message. I will likely go with the 2nd solution, even if it's not the most performance-friendly practice (since it requires to load the full related table in the data model, which is pretty large in my case).

But it will indeed do the job. Accepted as solution!