If Then using Length

[bluelabel]

Hi Power Users!

I am very new to Power Query, but an advanced Excel user. I am starting to get into Power Query and using data tables across a number of workbooks.

I have a column in my data table that should be a number however, sometimes it will be a text due to a letter being added to the end of the number. It will always be 5 numbers and then followed by a letter if that is the case.

	A	B	C
1	Car	59847	10
2	Truck	59848B	20
3	Bus	59489C	30

<tbody>
</tbody>

When I bring this data into Power Query the items in Column B with the suffix letter error out.

My excel brain tells me =if(Len(B1)=6, Left(B1, 5), B1)

How do i write this into a query so it comes through as a numeric value please?

 

[sandy666]

maybe try

Column1	Column2	Column3	Column4		Column1	Column2	Trim	Column4
1​	Car	59847​	10​		1​	Car	59847​	10​
2​	Truck	59848B	20​		2​	Truck	59848​	20​
3​	Bus	59489C	30​		3​	Bus	59489​	30​

[SIZE=1]let
    C2R = List.Transform({65..90,97..122}, each Character.FromNumber(_)),
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TypeText = Table.TransformColumnTypes(Source,{{"Column3", type text}}),
    TrimLetters = Table.AddColumn(TypeText, "Trim", each Text.Trim([Column3],C2R)),
    TypeNumber = Table.TransformColumnTypes(TrimLetters,{{"Trim", Int64.Type}}),
    RC = Table.RemoveColumns(TypeNumber,{"Column3"}),
    Reorder = Table.ReorderColumns(RC,{"Column1", "Column2", "Trim", "Column4"})
in
    Reorder[/SIZE]

or just

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Extract5 = Table.TransformColumns(Source, {{"Column3", each Text.Start(Text.From(_, "en-GB"), 5), type text}}),
    TypeNumber = Table.TransformColumnTypes(Extract5,{{"Column3", Int64.Type}})
in
    TypeNumber[/SIZE]

 

[theBardd]

A third way

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TypeText = Table.TransformColumnTypes(Source,{{"Column3", type text}}),
    KeepNumbers = Table.TransformColumns(TypeText, {{"Column3", each Text.Select(_, {"0".."9"}), type text}}),
    TypeNumber = Table.TransformColumnTypes(KeepNumbers,{{"Column3", Int64.Type}})
in
    TypeNumber