topic Re: Create custom folding function for STRING_AGG in PostgreSQL in Developer

Create custom folding function for STRING_AGG in PostgreSQL

Alex_RM — Mon, 18 Nov 2024 09:47:57 GMT

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.

Re: Create custom folding function for STRING_AGG in PostgreSQL

DataNinja777 — Mon, 18 Nov 2024 12:02:39 GMT

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,

Re: Create custom folding function for STRING_AGG in PostgreSQL

Alex_RM — Mon, 18 Nov 2024 12:57:27 GMT

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.

Re: Create custom folding function for STRING_AGG in PostgreSQL

Anonymous — Tue, 19 Nov 2024 06:44:42 GMT

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

Re: Create custom folding function for STRING_AGG in PostgreSQL

Alex_RM — Tue, 19 Nov 2024 08:46:43 GMT

Hello @Anonymous , 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!