Forum Discussion

Alex_RM's avatar
Alex_RM
Advocate I
1 year ago
Solved

Create custom folding function for STRING_AGG in PostgreSQL

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.

Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

4 Replies

  • DataNinja777's avatar
    DataNinja777
    Super User
    1 year ago

    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's avatar
      Alex_RM
      Advocate I
      1 year ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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