Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Convert Columns of Pence into Pounds and Pence

  • 1.  Convert Columns of Pence into Pounds and Pence

    Bronze Contributor
    Posted 11 days ago
    I have a data table with a significant number of  numeric columns with data expressed in Pence.  Is it possible to convert these columns to Pounds and Pence without having to create new columns?

    Thanks in advance

    Paul

    ------------------------------
    Paul Boyes
    Proprietor
    PB Business Solutions
    Warwick
    7966381218
    ------------------------------


  • 2.  RE: Convert Columns of Pence into Pounds and Pence

    Top Contributor
    Posted 11 days ago
    I guess that depends on what you mean by not creating a new column.

    In the Query Editor, you can do an "in place" transform for this.
    Select your Pence column.
    Go to transform tab.
    In the number section of the transform tab, select Standard icon.
    From the drop down, select divide.
    In value, put 100.
    It will overwrite your values with the result of the math.

    You can create also DAX measure that divides your pence column by 100.
    You can then use that measure in any visual and it will display the decimal value.

    Not sure if that answers your question.


    ------------------------------
    Audrey Abbey
    SR. BI Developer/Analyst
    New Seasons Market
    Portland OR
    ------------------------------



  • 3.  RE: Convert Columns of Pence into Pounds and Pence

    Top Contributor
    Posted 11 days ago
    Hi @Paul Boyes,

    Adding to what @Audrey Abbey said, once you do the division, if you want the Pound and Pence in different columns, you can do 'Split by Delimeter' and you should be able to get the Pound and Pence in different columns.

    Thank you,​​

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------



  • 4.  RE: Convert Columns of Pence into Pounds and Pence

    Posted 10 days ago
    Hi Paul,

    In Power Query (PQ) you can create a Custom Column and enter the conversion formula ie Pounds=[Pence]/100. After you have created your new Pounds and pence data column (format the new column as a decimal number) you would then remove the original [Pence] column which will not have an impact on your new Pounds Custom column because PQ applies the data preparation steps sequentially. Hope this helps.

    Simon

    ------------------------------
    Simon Forthun
    Management Accountant
    Doncaster VI
    400659967
    ------------------------------