How to Format a Text String as SQL Query

[joyhackett]

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

[PunChili]

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://xxxx@onelake.dfs.fabric.microsoft.com/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))

[rohit_singh]

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]

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 · Accepted solution]

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