Will Transforming Data for a column in a table change new incoming data as well?

[wpf_]

Hi I have a column in one of my table that specifies the direction of the cars.  The value is either I for inbound or O for outbound.  If i transform that from I --> Inbound and O --> outbound,   will new data that I import in either through importing or directquery be changed automatically as well, or will they still be I and O as they are from the source?  I'm debating whether I should transform it, or just add a calculated column for it.  Thanks. 

[TomMartens]

Hey @wpf_ ,

new data will also be transformed, all the steps after the Source step will be applied in sequence.

This is how Power Query works, simple transformations as described will be pushed down to the underlying database. This "behavior" is called "query folding".

Basically there is no difference if you are using Power Query or DAX to perform the transformation against a DirectQuery data source.

Hopefully, this provides the answer you are looking for.

Regards,

Tom

[wpf_]

@TomMartens Thanks for the answer.  But it won't change the data in the data source correct?  And is transforming more efficient in terms of processing and load time or calculated columns in this case?  Thanks. 

[TomMartens]

Hey @wpf_ ,

no source data will not be transformed.

Power Query creates a SQL statement (assuming your data source is a relational database) that looks like this:

SELECT

t.c1 , t.c2 , CASE WHEN ... THEN ... WHEN ... THEN ... ELSE ... END 
FROM

table as t 

As it's always a  good idea to create columns inside the query and not in DAX, as the Power Query columns will be part of the compression that happens during model creation, these columns will consume less memory and the model will perform faster. My recommendation is always to create columsn using Power Query.

Depending on your overall model size, it's also very likely that you will experience a difference in performance.

Regards,

Tom

[wpf_]

@TomMartens ,

Sorry i'm fairly new to BI here, when you say its better to create columns using power query, you are referring to creating calculated columns?  So you are saying calculated columns would be the better choice then to transform the column?  Thanks

[TomMartens]

Hey @wpf_ ,

nope!

Power Query creates "custom columns", this is my recommendation.

DAX creates "calculated columns".

Regards,

Tom

[wpf_]

@TomMartens I got the difference with power query and dax columns now.   thanks for the heads up, I have been creating them in dax.  However for my scenario, where I want to convert the I and O in a column to In and Out instead, would it be better to reuse that column and do a conversion or add a new column?  

[TomMartens · Accepted solution]

Hey @wpf_ ,

I do not understand your question, create a custom column using Power Query, not DAX.

Regards,

Tom

[wpf_]

@TomMartens 

Sorry for the confusion.  I had thought you can convert the values in one column to something else without adding an additional column.  I had mistaken it for a conditional column.  I believe that is what I need. Thanks!