CODEWITHSUNDEEP
powerbipowerquerypowerbi-desktop
SRel
SRel

Reputation: 423

Power-BI: Add column in Data-Area or Add Column in Power-Query

I am wondering where the difference in both approaches lie.

Basically I want to add a column which indicates as a result with TRUE or FALSE if a data row is inside my time period I have to consider (all values older than current calender week - 1).

For my understanding I have two options:

Option 1:

I modify my data query and add a new column with a formula like this in Power-Query:

DATEDIFF(WEEKNUM([created].[Date]),WEEKNUM(TODAY()),WEEK)

Option 2:

I use the Data-Section in Power-BI and add a column wiht this formula:

DATEDIFF(WEEKNUM([created].[Date]),WEEKNUM(TODAY()),WEEK)

What is the difference of both approaches, using either the backend PowerQuery vs the Data-Section in Power-BI. Is one more favourable?

Upvotes: 0

Views: 137

Answers (1)

jr_runnfool
jr_runnfool

Reputation: 36

Common answer in PowerPivot world suggests calculated columns are very costly, therefore better to choose PowerQuery to do the data preparation work. I suppose it depends on how many rows you have and how many other calculations you are asking PP/DAX to do as you are now storing a value that can be easily calculated.

Independent of "cost", I tend to use DAX for dynamic calculations and land more static values using PQ/M which allows for some very creative extract/transform/load (ETL). Think of it this way: if you put the formula in Excel, calculate for 1MM rows it has to recalculate every time you do, well, anything. So what do you do? Use the formula to calc the value then paste values so you just keep the answer. PQ can deliver the final result and drop the calcs or -- better -- intermediate data. JR

Upvotes: 2

Related Questions