Power query apply formula to mixed data column

Question

Hi&nbsp;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 formula such as: 0.5 * [column], it gives error because there's text also.How can I go around this?Thanks.&nbsp;

m_tarler · Answer

=IF(ISNUMBER(A:A), 0.5*A:A, A:A)

sergeibaklan · Answer

Splitair85&nbsp;
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

Forum Discussion

Power query apply formula to mixed data column

Share

Resources