Forum Discussion

joyhackett's avatar
joyhackett
Icon for Helper II rankHelper 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_name view_name view_definition powerbi view_fact_program_registration SELECT p.club_key, p.location_key, p.service_k...
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

PunChili's avatar
PunChili
Icon for Advocate II rankAdvocate 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))