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

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

      • 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!