cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
great_AS Member
Member

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-yulgu-msft Super Contributor
Super Contributor

Re: Formula conversion from excel to M

Hi @great_AS,

 

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"
)

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yulgu-msft Super Contributor
Super Contributor

Re: Formula conversion from excel to M

Hi @great_AS,

 

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"
)

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

great_AS Member
Member

Re: Formula conversion from excel to M

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

Highlighted
great_AS Member
Member

Re: Formula conversion from excel to M

Hi Yuliana,

 

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

 

Thanks in advance.

 

AS

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 296 members 2,977 guests
Please welcome our newest community members: