Forum Discussion

chenchen_voc's avatar
chenchen_voc
New Member
2 years ago
Solved

Using M-code in the Advanced Editor to load data in the Direct Query Mode

hi Experts,

My question is, how to load data in direct query mode via M-code in the advanced editor?

 

I am aware of how to load data from the Home ---> GetDate button.

 

But when I create a blank query, and try to load data using M-code in the advanced editor the new table will be loaded in Import mode. This is not what I want to have. 

 

This is the M code I have to load the data.  see this example.

let
    Source = Snowflake.Databases(#"ServerName",#"WarehouseName",[Role=#"RoleName"]),
    Database = Source{[Name=#"DatabaseName",Kind="Database"]}[Data],
    Schema = Database{[Name="EXAMPLE",Kind="Schema"]}[Data],
    Table = Schema{[Name="DIM_CLASS",Kind="Table"]}[Data]
in
    Table

 

Could you helo me to change my M code? Thanks!

  • Anonymous's avatar
    Anonymous
    2 years ago

    Thanks Anand24 , Allow me to offer another insight:

    Hi, chenchen_voc 

    If you want to use Direct Query mode when connecting to Snowflake, then you need to use the officially certified Snowflake connector, you can refer to the following official documentation:

    Power Query Snowflake connector - Power Query | Microsoft Learn

    If you use M code, as you say by super user, you won't be able to convert to Direct Query mode. 

     

     

     

    How to Get Your Question Answered Quickly

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

3 Replies

  • chenchen_voc ,
    You don't need to do anything in M query to change connection mode to DirectQuery. All you need to do is go to model view -> click on the table -> Expand advanced properties -> Switch storage mode to DirectQuery

    Refer below screenshot:

     

     

    • chenchen_voc's avatar
      chenchen_voc
      New Member

      Hi Anand, thanks for your reply.

       

      However, I can not convert this table into direct query mode. It has been set the import mode by default when I load the table with the M query. So I can not change it as your solution showed. 

      Thanks!

       

      Chen

      • Anonymous's avatar
        Anonymous
        Not applicable

        Thanks Anand24 , Allow me to offer another insight:

        Hi, chenchen_voc 

        If you want to use Direct Query mode when connecting to Snowflake, then you need to use the officially certified Snowflake connector, you can refer to the following official documentation:

        Power Query Snowflake connector - Power Query | Microsoft Learn

        If you use M code, as you say by super user, you won't be able to convert to Direct Query mode. 

         

         

         

        How to Get Your Question Answered Quickly

        If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

        Best Regards

        Jianpeng Li

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.