I have a column with multiple data type in Power BI as the following below. How I can convert/modify to Currency ( Fixed decimal number)

old_price

null 990 SR 4,040 SR 50/4 pack SR 445/2 pack null 895 SR 50/4 pack ......

I tried some of ways below

• replace value
• Added Custom column with formular: Convert, Number.From text However, it did not work and get error message

I would like to convert this mixed data into Fixed decimal number

I have a column with multiple data type in Power BI as the following below. How I can convert/modify to Currency ( Fixed decimal number)

old_price

null 990 SR 4,040 SR 50/4 pack SR 445/2 pack null 895 SR 50/4 pack ......

I tried some of ways below

• replace value
• Added Custom column with formular: Convert, Number.From text However, it did not work and get error message

I would like to convert this mixed data into Fixed decimal number

• powerbi
• dax
• data-cleaning
• multiple-databases
• customcolumn

1 Answer 1

Having a mixed type column is generally a very bad idea. You could use something like that:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    TransformColumn = Table.TransformColumns(
        Source,
        {
            {"Column1", each if Value.Is(_, type number) then Number.ToText(_, "C") else _}
        }
)
in
    TransformColumn

Number.ToText(_, "C") - is used to format string as currency.