Forum Discussion

joyhackett's avatar
joyhackett
Helper II
4 years ago
Solved

How to Format a Text String as SQL Query

Hi,

I have loaded a table of SQL definitions.  

Sample record:

schema_nameview_nameview_definition
powerbiview_fact_program_registrationSELECT p.club_key, p.location_key, p.service_key, p.program_session_key, p.durable_user_key, p.row_is_archived, p.is_unregistered FROM (fact_program_registration p JOIN dim_program_session dp ON ((p.program_session_key = dp.program_session_key))) WHERE (1 = 1);


On the dashboard, I would like to display the definition in proper SQL format:

SELECT p.club_key,
       p.location_key,
       p.service_key,
       p.program_session_key,
       p.durable_user_key,
       p.row_is_archived,
       p.is_unregistered
FROM fact_program_registration p
         JOIN dim_program_session dp ON p.program_session_key = dp.program_session_key
WHERE 1 = 1;


Anyone know of a way to do this? (I'm thinking I may need to create a PowerQuery function?)

 

Thanks in advance 🙂 Joy

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi joyhackett,

    AFAIK, current power bi does not support formatting T-SQL strings.
    In my opinion, I'd like to suggest your enter 'query editor' to add a custom column to invoke the T-SQL string formatting API that processes and store a formatted string into this custom column.

    Regards,

    Xiaoxin Sheng

4 Replies

  • rohit_singh's avatar
    rohit_singh
    Solution Sage
    4 years ago

    Hi joyhackett ,

    In DAX you can create a calculated column as follows :

    view_transformed =

    "SELECT p.club_key," & UNICHAR(10) &
    "　　　 " & "p.location_key," & UNICHAR(10) &
    "　　　 " & "p.service_key," & UNICHAR(10) &
    "　　　 " & "p.program_session_key," & UNICHAR(10) &
    "　　　 " & "p.durable_user_key," & UNICHAR(10) &
    "　　　 " & "p.row_is_archived," & UNICHAR(10) &
    "　　　 " & "p.is_unregistered" & UNICHAR(10) &
    "FROM (fact_program_registration p" & UNICHAR(10) &
    "　　　 " & "JOIN dim_program_session dp ON ((p.program_session_key = dp.program_session_key))) " & UNICHAR(10) &
    "WHERE (1 = 1);"
     
    This will give you the following result :

     

    I tried a solution using power query but it did not work in the report view.

    Kind regards,

    Rohit


    Please mark this answer as the solution if it resolves your issue.
    Appreciate your kudos! 🙂


     

    • joyhackett's avatar
      joyhackett
      Helper II
      4 years ago

      Thank you.

      Your solution will only work for the sample row that I provided. There are 100 rows of SQL view definitions.
      However, I see what you are doing with the line breaks, so this puts me on a possible path.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi joyhackett,

        AFAIK, current power bi does not support formatting T-SQL strings.
        In my opinion, I'd like to suggest your enter 'query editor' to add a custom column to invoke the T-SQL string formatting API that processes and store a formatted string into this custom column.

        Regards,

        Xiaoxin Sheng

  • PunChili's avatar
    PunChili
    Advocate II
    1 year ago

    Dear all,

    I do have exactely the same issue, but for me in a fabric notebook. I want to document the query in a notebook. Unfortunately the formatting is still like the first screenshot of joyhackett . Any idea how I can format it in a better way?

     

    Happy about Input 🙂

     

    At the moment I use this:

    sql_file_path = 'abfss://[email protected]/xxxx.Lakehouse/Files/silver/views/purchasetransactions.sql'

    sql_query = nbu.fs.head(sql_file_path)

    # Format the SQL query using sqlparse
    formatted_sql_query = sqlparse.format(sql_query,
                                            reindent=True,
                                            keyword_case='upper',
                                            indent_width=4,
                                            strip_comments=False
                                            )

    # Format the SQL query in HTML
    html_sql_query = f"""
                        <pre style="background-color: #f6f8fa; border: 1px solid #ddd; padding: 10px; white-space: pre-wrap;">
                        <code>{formatted_sql_query}</code>
                        </pre>
                        """

    # Display the formatted SQL query as HTML
    display(HTML(html_sql_query))