Add SQL statements in advanced window or rebuild in M

[gramespa]

Hi all,

I am connecting Power BI to a SQL Database and need some sql statements to create a new table based on joining several other tables with similar keys. Some have relations some dont. 

I have no trouble building what i need in SQL but i am not so sure about doing it in M.

Is it "best practice" to add SQL statements directly as advanced option or connect to Database and rebuild what i need in M? And if so is there any guidance how to transfer common statements like Select, cast, join, where etc?

Thank you all1

[VasTg]

@gramespa 

If you are using advanced window to run the SQL, some of the features like query folding can't be achieved.

But I always go with it because I know my SQL is right, tune it, bring in only the necessary columns and rows etc.

All I do in M query is to select the datatype and/or add custom/conditional columns for sorting,grouping etc..

On the flip side, If yo do it with M query, it has to read all the rows and transform the data in the way you need in Power BI.

But is it a "Best practise", I don't know. Test both the approaches and see which one loads faster.

If this helps, mark it as a solution

Kudos are nice too.

[gramespa]

Thank you for sharing you opinion in this! Since data size is not an issue in this case i might go for the advanced window and be sure what i am doing is correct.