Search code examples
powerbipowerquerypowerbi-desktop

How to perform custom decimal conversion in Power BI


Hi I am new to Power BI and I have a simple problem

I have a table like this

Number
3.4
10.5
12.4

I want to create a custom column that would take number before decimal place and multiply it with another number(eg. 10) and then add the digit behind the decimal for example 3.4 becomes (3 X 10)+4 = 34. So the custom column becomes like this

Number Custom
3.4 34
10.5 105
12.4 124
7 70

Also I understand I can split the actual column into 2 columns using split on delimiter and then make a 3rd calculated column but for learning purpose I want to create it as a custom column (if it is allowed/possible)


Solution

  • Number.IntegerDivide version:

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    MultiplyBy=10,
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Number.IntegerDivide([Number],1)*MultiplyBy+10*([Number]-Number.IntegerDivide([Number],1)))
    in #"Added Custom"
    

    Number.IntegerDivide([Number],1) is the integer part we multiply by the variable MultiplyBy

    [Number]-Number.IntegerDivide([Number],1) is the decimal part, we multiply by 10 before adding

    Text.Split version:

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    MultiplyBy=10,
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Number.From(Text.Split(Text.From([Number]),"."){0})*MultiplyBy +  Number.From(try Text.Split(Text.From([Number]),"."){1} otherwise 0))
    in #"Added Custom"