Forum Discussion

gramespa's avatar
gramespa
Frequent Visitor
6 years ago

Add SQL statements in advanced window or rebuild in M

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

Need Help

2 Replies

  • VasTg's avatar
    VasTg
    Icon for Memorable Member rankMemorable Member
    6 years ago

    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's avatar
      gramespa
      Frequent Visitor
      6 years ago

      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.