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.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Car
[/TD]
[TD]59847
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Truck
[/TD]
[TD]59848B
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Bus
[/TD]
[TD]59489C
[/TD]
[TD]30
[/TD]
[/TR]
</tbody>[/TABLE]


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

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td=bgcolor:#5B9BD5]Column2[/td][td=bgcolor:#5B9BD5]Column3[/td][td=bgcolor:#5B9BD5]Column4[/td][td][/td][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]Column2[/td][td=bgcolor:#70AD47]Trim[/td][td=bgcolor:#70AD47]Column4[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]Car[/td][td=bgcolor:#DDEBF7]

59847​

[/td][td=bgcolor:#DDEBF7]

10​

[/td][td][/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]Car[/td][td=bgcolor:#E2EFDA]

59847​

[/td][td=bgcolor:#E2EFDA]

10​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

2​

[/td][td]Truck[/td][td]59848B[/td][td]

20​

[/td][td][/td][td]

2​

[/td][td]Truck[/td][td]

59848​

[/td][td]

20​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

3​

[/td][td=bgcolor:#DDEBF7]Bus[/td][td=bgcolor:#DDEBF7]59489C[/td][td=bgcolor:#DDEBF7]

30​

[/td][td][/td][td=bgcolor:#E2EFDA]

3​

[/td][td=bgcolor:#E2EFDA]Bus[/td][td=bgcolor:#E2EFDA]

59489​

[/td][td=bgcolor:#E2EFDA]

30​

[/td][/tr]
[/table]

[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