Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Power BI


Want to convert "k" into "1000"

And If "K" is not present, it should return the same value.

For that use the following measure. It works for "K" only but not for other values.

WholeNumber = VALUE(LEFT('Table 1'[Views],LEN('Table 1'[Views])-1))*IF(RIGHT('Table 1'[Views],1)="k",1000,1)

General Comment
  • Anonymous's avatar
    Anonymous
    3 years ago

    Following logic works.

    Create new column

    WholeNumber =
    VAR ValueStr = 'Table 1'[Views]
    VAR ValueNum =
    IF(
    RIGHT(ValueStr, 1) = "k",
    VALUE(LEFT(ValueStr, LEN(ValueStr) - 1)) * 1000,
    VALUE(ValueStr)
    )
    RETURN
    IF(ISBLANK(ValueNum), BLANK(), INT(ValueNum))

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Following logic works.

    Create new column

    WholeNumber =
    VAR ValueStr = 'Table 1'[Views]
    VAR ValueNum =
    IF(
    RIGHT(ValueStr, 1) = "k",
    VALUE(LEFT(ValueStr, LEN(ValueStr) - 1)) * 1000,
    VALUE(ValueStr)
    )
    RETURN
    IF(ISBLANK(ValueNum), BLANK(), INT(ValueNum))

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    3 years ago

    Anonymous  I'd recommend doing this in Power Query. 

     

    Try this custom function. 

     

    Step 1: 

    Click Transform Data > New Source > Blank Query > Advanced Editor

     

    Delete everything that's there and paste this code to create a new function:

     

    (ViewCount as any) => let
    Source = ViewCount,
    LastCharacter = Text.End(Source, 1),
    ViewsNumericComponent = Value.FromText( Text.BeforeDelimiter(Source, LastCharacter) ),
    Multiplier = if LastCharacter = "K" then 1000 else if LastCharacter = "M" then 1000000 else 1,
    ViewsNumber = ViewsNumericComponent * Multiplier
    in
    ViewsNumber

     

     

    Give the function a name (ie ConvertToNumber)

     

    Step 2

    Select your table in Power Query. Click Add Column > Invoke Custom Function

    Select the function you just named, and the column with the views in it.

     

     

     

    Step 3

    Close & Apply

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      What is the next step after this?
      While giving name to a parameter error occurs.

      AllisonKennedy 

      Need help.

      • AllisonKennedy's avatar
        AllisonKennedy
        Community Champion
        3 years ago

        Anonymous 

         

        I know you solved using DAX, but if you want to continue to try with Power Query, your next step is to click back into your 'table' query.

         

        Click 'Add Column' > Invoke Custom Function. Choose the column of 'Views' that you have in that table.