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

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