Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Formula conversion from excel to M

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

 

 

 

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

3 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Yuliana,

       

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

       

      Thanks in advance.

       

      AS