Forum Discussion

Mangnuel's avatar
Mangnuel
Helper I
1 year ago
Solved

Replace any Value/Text/String by number

Hello everyone, I need your help again, thanks in advance. While importing an excel-file via power query into power bi we need to secure that within two differnt columns the inputs are always ...
Need Help
  • Akash_Varuna's avatar
    Akash_Varuna
    1 year ago

    hi try these formulas

                                     1 In Power Query, use the formula
                                        if Text.Trim([Column1]) <> "" then 1 else [Column1] in a Custom Column to replace non-                                    empty cells in Column1 with a static number like 1.

                                      2 To overwrite a column directly, use

                                         Table.TransformColumns(Source, {{"Column1", each if Text.Trim(_) <> "" then 1 else _,                                           type number}}).
    if this post helps please accept as this solution and give a kudos

     

     

Akash_Varuna's avatar
Akash_Varuna
Super User
1 year ago

hi try these formulas

                                 1 In Power Query, use the formula
                                    if Text.Trim([Column1]) <> "" then 1 else [Column1] in a Custom Column to replace non-                                    empty cells in Column1 with a static number like 1.

                                  2 To overwrite a column directly, use

                                     Table.TransformColumns(Source, {{"Column1", each if Text.Trim(_) <> "" then 1 else _,                                           type number}}).
if this post helps please accept as this solution and give a kudos