Forum Discussion
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 that.
Thank you very much
JFM_12
10 Replies
Hello Saudansari
Thank you.
I do not know which datatype Microsoft is using for these columns in Direct Query.
The database is in Direct Query and has to be connected using SQL if I understand you right.Is there another way to do that. CQD is run by Microsoft, Power BI Template of CQD is freely available.
Need to merger local information with CQD. Is there another way to do that?
Thank youJFM_12
Here's a general approach you might take within Power BI:
Power Query in Power BI Desktop:
- Open your Power BI Desktop file.
- Go to the "Home" tab, click on "Get Data," and select your data source (Microsoft Teams in this case).
- Use Power Query Editor to transform and clean your data.
- Load the data into Power BI.
Data Model in Power BI:
- Once the data is loaded, create relationships and build your data model in Power BI Desktop.
- You can combine data from different tables or sources using relationships.
Local Data:
- If you have local data that you want to merge with the data from Microsoft Teams, you can import that data into Power BI as well.
- Create relationships between the tables in the data model.
Power BI Report:
- Build your Power BI report using the combined data from Microsoft Teams and your local source.
Refresh Data:
- Schedule data refresh in Power BI Service so that your report gets updated with the latest data from Microsoft Teams.
Hello Saudansari
Thank you very muchBut I can not transform the data because it is Direct Query where data is stored.
The columns are not visible only accessible that is why I need to copy all the data locally
to do some manipulation. Only then I can transform the data and apply other format or more.
Best case would be to copy only neded columns worst case to copy all the data locally.
Do you have any other solutions
Thank you
JFM_12
Using SQL to copy data from one table to another. Replace the table and column names with the appropriate ones from your Microsoft Teams table:
-- Create a new local table (if it doesn't exist)
CREATE TABLE LocalTable (
Column1 datatype,
Column2 datatype,
-- Add other columns as needed
);-- Insert data into the local table from the Teams table
INSERT INTO LocalTable (Column1, Column2, ...)
SELECT Column1, Column2, ...
FROM TeamsTable;
Make sure to replace LocalTable, Column1, Column2, etc., and TeamsTable with the actual names used in your scenario. Also, ensure that the datatypes match between the source and destination columns.
