Replace any Value/Text/String by number

[Mangnuel]

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 the same.
The bleonging cells could contain a Text, maybe a number or just one letter.
Doesn't really matter what's within the cells, as long as it is not empty I would like to replace the input of the cell with a number.
So how can I check if a cell is not empty in generell and replace or overight the input with a defined static variable.

Many Thanks in advance

Raphael

[v-karpurapud]

Hi @Mangnuel 

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[v-karpurapud]

Hi @Mangnuel 

Hope you are doing well!

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-karpurapud]

Hi @Mangnuel 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-karpurapud]

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.

[Akash_Varuna · Accepted solution]

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