Get particular columns only to make new table from existing table

[jeongkim]

Hi,

Can we create new table with only particular columns from exsting query tables?

e.g. All existing tables have exact same format and wanna take particular 2 columns only from each existing tables and append all into 1 new table.

Of course I can do Append all tables as new first and delete other columns but the thing is this taking too much file size space and loading time, so I only wanna get necessary columns at the beginning. 

1 more question,

If I select and copy those 2 columns and create new table and manually paste, then still connected to existing dataset?

if new data comes, then this new created table also reflect new data automatically? 

[danextian · Accepted solution]

Hi @jeongkim 

Your DateTimeZone column has very high cardinality, which can significantly increase your model size. Consider removing the timezone and seconds if they aren't needed. To further reduce cardinality, you can also split the column into separate date and time columns.

Also, experiement with using DAX instead of append in the query editor to create a new table from already laoded ones.

New Table =
VAR tbl01 =
    SELECTCOLUMNS (
        tbl01,
        "Desired Column Name1", tbl01[column1],
        "Desired Column Name2", tbl01[column2]
    )
VAR tbl02 =
    SELECTCOLUMNS (
        tbl02,
        "Desired Column Name1", tbl02[column1],
        "Desired Column Name2", tbl02[column2]
    )
RETURN
    UNION ( tbl01, tbl02 )

DAX expressions like UNION() are evaluated in memory after the data model is loaded, whereas Power Query transformations happen during the refresh process, which can be more time-consuming, especially for large datasets and/or slow data sources. Also, PQ appends require reshaping and reloading the data before it lands in the model. The DAX method skips that extra step and works with what's already loaded.

[jeongkim]

Pls also 1 more question,

I recently added another column into input files in the same data table. 

If I duplicate the data table in query and add column 'B-a' with blank value into Week 1~5,

no issue to append data set but it takes too much modeling size and hard to change any code due to loading time. 

As you mentioned, can we do it in dax like 1 dax data table for Week 1~5 adding 'B-a' column and another dax data table for Week 6 ~ 10 and another dax data table to append W 1 ~ 10? without duplicating query?

Or any better idea pls? 

each week data is split each file by file 

[jeongkim]

Thanks it's really good to know. 

'Consider removing the timezone and seconds '
Where did you check my data has timezone? and how can I remove it? 

'you can also split the column into separate date and time columns'

How does it work differently with separation and without separation? while the same text still would be there.

[danextian]

Hi @jeongkim 

That globe map with clock is a symbol for datetimezone. There's also +00

This is a pretty good blog that explains how VertiPaq compresses data - https://www.microsoftpressstore.com/articles/article.aspx?p=2449192&seqNum=3 

[Idrissshatila]

Hello @jeongkim ,

for your second question that if you select and copy then pasted it in a new table, no it won't update since its not anymore connected to the datasource.

well the best approach is it to be done in the datasource if available, else it should be appended as you mentioned