Is it more efficient to write formulas in Excel tables or in Power Query?

[freelensia]

Hi, 

I have a database of about 10K rows. They come from csv file. I import with PQ, add some formulas and reexport the output data back to csv to feed into other Excel files and PQs.

The question is in general, should we use Power Query to add custom columns, or should we do that in Excel on the resulting table?

Some of my Excel formulas are quite complex and I am afraid it is very difficult to write them in M language, or it may be very process-consuming.

For example this UDF TSWITCH that mimicks the SWITCH function of Excel 365:

=TSWITCH(
    [@[Language (Internal)]],
    "vi",
    IF(
        [@[First Name (Internal)]] <> "",
        "Gửi bạn "& [@[First Name (Internal)]]&",",
        IF([@[Given Name]]<>"","Gửi bạn "& [@[Given Name]]&",","Chào bạn,")
    ),
    "ja",
    IF(
        [@[Organization Name (JA)]] <> 0,
        [@[Organization Name (JA)]],
        IF([@[Organization Name (EN)]]<>0,[@[Organization Name (EN)]],"")
    ),
    IF(
         [@[First Name (Internal)]] <> "",
        "Dear " & [@[First Name (Internal)]],
        IF([@[Given Name]]<>"","Dear " & [@[Given Name]],"Dear friend,")
    )
)

I guess I could try to write it in M language. It would take many steps, new columns and lines of codes.

But let's say it is possible to write it in PQ, is it more efficient than using the native Excel functions?

Thanks!

[v-juanli-msft]

Hi @freelensia

Based on my knowledge, the performance of Excel formula, M or DAX are uncompariable, it depends on the requirements.

If you've got large datasets then I would suggest you not to do create calculated columns if possible.

1.one way is to create that in Excel on the resulting table.

2.you can add as a DAX measure then it's not going store any data and calculated when needed.

However measure cannot be used for slicers.

3.Another option add the Competed state in M (Query editor).  

Measure below

Measure =
SWITCH (
    MAX([Language]),
    "vi", IF (
        MAX([First Name]) <> " ",
        "Gửi bạn " & MAX([First Name])
            & ",",
        IF ( MAX([Given Name]) <> " ", "Gửi bạn " & MAX([Given Name]) & ",", "Chào bạn," )
    ),
    "ja", IF (
        MAX(JA[Organization Name]) <> 0,
        MAX(JA[Organization Name]),
        IF ( MAX(EN[Organization Name]) <> 0, MAX(EN[Organization Name]), " " )
    ),
    IF (
        MAX([First Name]) <> " ",
        "Dear " & MAX([First Name]),
        IF ( MAX([Given Name]) <> " ", "Dear " & MAX([Given Name]), "Dear friend," )
    )
)

Or M in power query