extract only number value in query

Hello Anonymous,

Thanks for your follow-up and clarification regarding the "金額" column.

Based on your updated query, you can modify your code to extract only the numeric values from the "金額" column like this:

let

    // Existing steps

    #"Renamed Columns2" = Table.RenameColumns(#"Expanded Work Order PDF - bottom", {

        {"Work Order PDF - top b.派工單號", "派工單號"},

        {"Work Order PDF - top b.PO NO", "PO NO"},

        {"Work Order PDF - middle.標的物編號", "標的物編號"},

        {"Work Order PDF - middle.標的物名稱", "標的物名稱"},

        {"Work Order PDF - middle.監工人員", "監工人員"},

        {"Work Order PDF - middle.備 註", "備 註"},

        {"Work Order PDF - bottom.派工工程項目", "派工工程項目"},

        {"Work Order PDF - bottom.4G or 5G", "4G or 5G"},

        {"Work Order PDF - bottom.安裝型態", "安裝型態"},

        {"Work Order PDF - bottom.金額", "金額"},

        {"Work Order PDF - bottom.數量", "數量"}

    }),

   

    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns2", {

        "Source Name", "通知日期", "派工目的", "派工單號", "PO NO",

        "標的物編號", "標的物名稱", "監工人員", "備 註", "派工工程項目",

        "4G or 5G", "安裝型態", "金額", "數量", "Date created"

    }),



    // New step: Remove non-numeric characters from 金額 column

    #"RemoveNonNumericFrom金額" = Table.TransformColumns(#"Reordered Columns", {

        {"金額", each Text.Select(Text.From(_), {"0".."9"}), type text}

    }),



    // New step: Convert cleaned 金額 column to number

    #"金額ToNumber" = Table.TransformColumnTypes(#"RemoveNonNumericFrom金額", {

        {"金額", Int64.Type}

    })

in

    #"金額ToNumber"

 

• We first convert "金額" to text, then keep only digits using Text.Select.
• Finally, we convert it back to a number type.

This ensures that if your "金額" column contains mixed text and numbers (e.g., "金額=NTD5000"), it will properly extract only the numbers (e.g., 5000).

 

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.