Forum Discussion

Copper Contributor

Jul 24, 2024

Power query apply formula to mixed data column

Hi I imported a table of students marks, and the columns have either marks written or 'Absent'. Now I want to create another custom function column with 50% of the marks. But if I input the form...

excel

Formulas and Functions

office 365

SergeiBaklan

Diamond Contributor

Jul 24, 2024

Splitair85

If the column with marks is of any data type, that could be

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

    GetHalf = Table.AddColumn(
        Source,
        "50%",
        each try [Mark]/2 otherwise [Mark],
        type any )
in
    GetHalf

if of text type

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DeclareType = Table.TransformColumnTypes(Source,{{"Mark", type text}}),
    GetHalf = Table.AddColumn(
        DeclareType,
        "50%",
        each try Number.FromText([Mark])/2 otherwise [Mark],
        type any )
in
    GetHalf

Result is like