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...
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

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

  • Alex_RM's avatar
    Alex_RM
    Advocate I
    1 year ago

    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!