Forum Discussion
Query Folding - disable?
Hi Anonymous ,
After researching and testing,I am afraid that you could not turn off query folding currently.
However, you could vote for this idea which is similar to yours. Maybe it will be a feature of Power BI in the future.
Alex_Ooi yep, that's about the only other way to do it if ODBC isn't working for you. Definitely tedious to create the M code by hand, even though the Value.NativeQuery() function isn't all that complicated.
The kicker, as you've shown in your sample SQL, is replacing carriage returns (i.e. new lines) with the #(lf) syntax, and double quotes with two sets of double quotes (since the Value.NativeQuery() itself uses double quotes).
For my queries, that's a pain in the butt, but alas, if there's no other way...
ryan0585 Anonymous
Here's what I really do to avoid jumping down Niagara Falls...
1. Write script in IDE
2. Paste script to Excel as text
3. Delimit the script into components so each code element is in one cell
4. Add helper cells
5. Concatenate everything
6. Paste it in M using Value.NativeQuery( ) function
Example:
Step 1: Write script in IDE
SELECT
FieldA AS "This is Field One",
FieldB AS "This is Field Two",
...
FROM
TableXYZ
Step 2: Paste into Excel
Step 3: Delimit by space
Step 4: Add helper cells
In this case, my helper cells would be
#(lf) [code for lline feed]
" [double quote]
, [comma]
Step 5: Concatenate these based on the syntax I shared with you guys earlier
You can use functions like CONCAT, CONCATENATE, TEXTJOIN
Step 6: Paste it to M
DONE!
The only disadvantage I can see is you're not able to comment out part of your query. So make sure if you're dealing with something that would change over the course of time - SAVE ALL YOUR WORKING FILES IN ONE FOLDER.