Forum Discussion

Heinrich's avatar
Heinrich
Post Partisan
2 years ago

Copy all data from direct query table

Hello I have access to CQD (Microsoft Teams Table - Direct Query). I would like to copy static data to a local table. Either some columns or all the data. Do you have a Script or way to accomplish...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
saudansari's avatar
saudansari
Helper II
2 years ago

you can consider the following options:

  1. Power BI Dataflows:

    • Use Power BI Dataflows to transform and reshape the data.
    • Create a dataflow that pulls data from your Direct Query source, performs the necessary transformations, and stores the result in Power BI Dataflows.
    • Once the dataflow is set up, you can use it as a source for your Power BI report.

  2. Power Query and Local Tables:

    • Import the entire dataset into Power BI (even if it's large).
    • Use Power Query within Power BI to filter and transform the data as needed.
    • Create relationships between tables and build your report.

      Other than this I am not sure how I can help further on this. 
Heinrich's avatar
Heinrich
Post Partisan
2 years ago

Hello
Thank you Dataflow is done using the Power BI Service, right?
But Dataflows has to be subscribed and paid. It is not enabled in our Company.

Is there a way to do that with DAX?
Get a Query with DAX and import this to a local table?

Regards
JFM_12

  • saudansari's avatar
    saudansari
    Helper II
    2 years ago

    Yes, dataflow is with Power BI service. You will have to write the query in whatever database you have and then pull that from the get data in the Power BI desktop. 

    Or you can download he file from Power BI service and go to model view and change the storage mode to import in that way it will store the data locally

    • Heinrich's avatar
      Heinrich
      Post Partisan
      2 years ago

      Hello Saudansari

      Dataflow is not enabled in our company
      The Power BI File is allready available at Microsoft Site and allready opened. 
      Change the Storage Mode and how do I have to import it?

      I am looking for a solution in DAX is this possible?
      Is it possible to have a DAX query that pulls the data and copies it to a local table?
      Regards
      JFM_12

      • saudansari's avatar
        saudansari
        Helper II
        2 years ago

        I am unsure if this will work but you can create a calculated table in DAX

        LocalTable =
        SUMMARIZE(
        'TeamsTable',
        'TeamsTable'[Column1],
        'TeamsTable'[Column2],
        -- Add other columns as needed
        )

        if this works please accept it as solution