Formula conversion from excel to M

[Anonymous]

Hi everybody,

I need to translate the formula in the excel attached in Power BI.

Any ideas on how to do it?

Thanks in advance.

Regards,

AS

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Please new a calculated column with DAX formula:

Ct. Converted =
FORMAT (
    VALUE (
        IF (
            ISERROR ( VALUE ( LEFT ( Sheet2[Ct. Range], 1 ) ) ),
            BLANK (),
            IF (
                VALUE ( LEFT ( Sheet2[Ct. Range], 1 ) ) = 0,
                "0." & MID ( Sheet2[Ct. Range], 3, 5 ),
                IF (
                    VALUE ( LEFT ( Sheet2[Ct. Range], 1 ) ) > 0,
                    LEFT ( Sheet2[Ct. Range], 1 ) & "."
                        & MID ( Sheet2[Ct. Range], 3, 5 )
                )
            )
        )
    ),
    "0.0000"
)

Best regards,

Yuliana Gu

[Anonymous]

Hi Yuliana,

how can I convert the output of the formula you shared into numbers instead of text?

Thanks in advance.

AS

[Anonymous]

Thanks a lot Yuliana.  This formula is something yo had to insert in the report right? Not in the query editor. Is it correct?