Power Query Editor or Data View?

[Enigma]

I was adding a custom column for doing some text manipulations based on values in an existing date column. I realised that DAX functions aren't recognised in Power Query Editor, so I had to write it in the Data View.

Apart from the language restrictions, what are the best practices on when to use PQE vs DV?

My report contains multiple queries and large data, so I want to use all tips to make the data refreshes as quick as possible.

In my requirement, what is a more efficient way - writing in DAX language in DV; or, writing in M language in PQE?

[amitchandak]

Refer : Both M and Dax witch in

https://www.thebiccountant.com/2018/02/15/generic-switch-function-query-editor-in-power-bi-and-power...

Refer :

https://radacad.com/m-or-dax-that-is-the-question

https://stackoverflow.com/questions/39632894/whats-the-difference-between-dax-and-power-query-or-m

[Greg_Deckler]

Well, like most things, it depends. But the general rule is that the further "upstream" that you can push the creation of custom columns the better. So that is M and PQ. But, there are lots of caveats and exceptions to that rule, would need to better understand your requirements.

[Enigma]

Thanks @Greg_Deckler 

The requirement: Extract the month number from a date column. Then, assign a text based on the month number.

I used this in PQE

SWITCH(Date.Month([Download Date]),1,"Q2",2,"Q2",3,"Q3",4,"Q3",5,"Q3",6,"Q4",7,"Q4",8,"Q4",9,"Q1",10,"Q1",11,"Q1",12,"Q2","Unknown Month")

While the 'Date.Month([Download Date])' worked, the 'SWITCH' gave an error. 

[Greg_Deckler · Accepted solution]

Yeah, honestly this might be one of those exceptions where it is better/faster/easier to just use DAX. Switch statements in PQ are a pain but can be done: https://www.thebiccountant.com/2018/02/15/generic-switch-function-query-editor-in-power-bi-and-power...