Direct Query on a specific case

[Anonymous]

Hi everyone,

Currently, I'm waiting for being able to create a connection with my database. It's on working

In waiting , I would like to create a table with the same columns than a table in the database, in order to have some reports. Once the work on the database is done, I will use the direct query. Would my reports will still work well ? Which precaution should I take to avoid errors ?

Thank you ! 

[Stachu]

Working with Direct Query has some limitations which do not apply in the standard setup (see here https://docs.microsoft.com/en-us/power-bi/desktop-use-directquery#limitations-of-directquery). So as long as you don't use any functionalities not supported in Direct Query then you should be fine, and the whole process should be as easy as changing the reference from static table to Direct Query table

[Anonymous]

@Stachu 

Thank you for the answer

I will use some DAX functions so it should be fine. Nevertheless, I worry about columns on my static table. 

Does my static table must have same things than the table on the database ? ( the same number of column, the same names)

By example , my static table 

Car has

columnA , columnB

and in my database I have the tab

tab_CAR with

CAR_Name, CAR_Year, CAR_Description, Car_Promotion

Thanks !

[Stachu]

I explained similar case here:
https://community.powerbi.com/t5/Desktop/Add-missing-tables-from-data-source-to-existing-dataset/m-p...

I'd create a single connection query (not loaded to the model), that doesn't show any tables, just establishes connection (this way you can easily switch from QA to PROD etc.)
then every table would be referencing that connection query

so in the case I posted it would be instead of this (you need to replace StaticTable and ColumnA:

let 
    Source = StaticTable,
    AddedType = Table.TransformColumnTypes(Source,{{"ColumnA", Int64.Type}})
...

you use this:

let 
    Source = StaticTable,
    AddedType = Table.TransformColumnTypes(Source,{{"CAR_Name", Int64.Type}})
...

the column name is the same as in your database so when you replace StaticTable with a proper one it will work without any more changes

[Anonymous]

Thank you very much !!!

[Stachu · Accepted solution]

I would use exactly same names as in your database, otherwise all the refrences will be incorrect when you switch

consider this code for setting a type of a column:

= Table.TransformColumnTypes(Source,{{"ColumnA", Int64.Type}})

 in this case when you swtich you need to change Source and "ColumnA".

If you would use the same names as in the database you just need to change Source to reference proper table

[Anonymous]

Thank you @Stachu 

I will use the same name to be sure

In another case where the names are differents

= Table.TransformColumnTypes(Source,{{"ColumnA", Int64.Type}})

When I switch,  columnA is my cible in the static table    and  Source should be  like this ?

= Table.TransformColumnTypes(Name_database{[Schema="dbo",Item="tab_CAR",column="CAR_Name"]}[Data]    ,{{"ColumnA", Int64.Type}})

I'm not sure for the code