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

     

     

v-karpurapud's avatar
v-karpurapud
Community Support
1 year ago

Hi Mangnuel 

Thanks for reaching out to Microsoft Fabric Community Forum.

We found the answers provided by Akash_Varuna works perfectly. Please consider trying them out to check if they work for you. 

I have recreated your scenario using sample data it works fine .

 

If they work, please consider marking them 'Accept as Solution' and give a KUDOS.
Thank You.