how to get value from a text column without a certain letter

[Anonymous]

Hi, I want to get a integer column baseed this column:

column

W+2

W-5
W-6

W

W+1

and expected result:

2

-5

-6

0

1

do you have any solutions?

[SergioSilvaPT · Accepted solution]

Hi @Anonymous,

According with you sample data you only need to do the following steps in Power Query:

1. Replace "W" with ""
2. Replace "" with 0
3. Change the type of data to whole number

Solution in DAX:

Add the following column:

Column =
IF('Table'[Data]="W",
    "0",
    REPLACE('Table'[Data],1,1,"")
)

Check the pbi in attach with an example.

[Ahmedx · Accepted solution]

ID = 
 VAR _табл. =
     ДОБАВИТЬ КОЛОННЫ (
         GENERATE (GENERATESERIES (1, 9, 1), VALUES (tbl[serial])),
         "я бы",
             ЕСЛИ ОШИБКА (
                 СТОИМОСТЬ (
                     IF (CONTAINSSTRING ([серийный номер], "-" ), "-" ) & MID ( [серийный номер], [значение], 1 )
                 ),
                 ПУСТОЙ ()
             )
     )
 VAR _Результат =
     СЦЕПИТЬ ( _tbl, [id], "" )
 ВОЗВРАЩАТЬСЯ
     ЕСЛИ (HASONEVALUE ('tbl'[serial]), ЕСЛИ (_Result = "", "0", _Result)) 

https://dropmefiles.com/WDG3S

[Idrissshatila]

Hello,

You could add a calculated column with this dax

Column = Var Column1 = RIGHT(customers[Customer Name],2)

var Column2 = LEN(customers[Customer Name])

return

IF(Column2 = 3,Column1,FORMAT(0,"0"))

If I answered your question, please mark my post as solution, Appreciate your Kudos👍

Follow me on Linkedin

[Anonymous]

Whatif the value is W+100 or W+1000, does any function can omit letter W?

[SergioSilvaPT · Accepted solution]

Hi @Anonymous,

According with you sample data you only need to do the following steps in Power Query:

1. Replace "W" with ""
2. Replace "" with 0
3. Change the type of data to whole number

Solution in DAX:

Add the following column:

Column =
IF('Table'[Data]="W",
    "0",
    REPLACE('Table'[Data],1,1,"")
)

Check the pbi in attach with an example.

[Anonymous]

This column is calculated use DAX, so powerquery does not work

[SergioSilvaPT]

@Anonymous , check my solution in DAX in the first response.

[Ahmedx]

in power query:

let
    Source = Excel.Workbook(File.Contents("C:\Users\User\Desktop\serial.xlsx"), null, true),
    Лист1_Sheet = Source{[Item="Лист1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Лист1_Sheet,{{"Column1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"serial", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "ID", each Number.From(Text.Select([serial],{"-"})&
Text.Select([serial],{"1".."9"}))??0)
in
    #"Added Custom"

https://dropmefiles.com/rT5IN