Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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 you
JFM_12
Here's a general approach you might take within Power BI:
Power Query in Power BI Desktop:
Data Model in Power BI:
Local Data:
Power BI Report:
Refresh Data:
Hello Saudansari
Thank you very much
But 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
you can consider the following options:
Power BI Dataflows:
Power Query and Local Tables:
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
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
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
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
Hello Saudansari
Hope you are doing well
Thank you very much for your support
1. Dataflow has to be licensed right. Then this is not enabled
2. Your solution
LocalTable =
SUMMARIZE(
'TeamsTable',
'TeamsTable'[Column1],
'TeamsTable'[Column2],
-- Add other columns as needed
)
-> Create local table and and this as DAX where
- TeamsTable is name of original Table, right?
- TeamsTable[Column1] etc. name of original column
I did it as you said
Regards
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.
User | Count |
---|---|
103 | |
87 | |
77 | |
70 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |