Text.AfterDelimiter in transformation step did not change column type

[Gauravshahin]

Hi Experts,

I used = Table.TransformColumns(#"Unpivoted Columns", {{"FcstWK", each Text.AfterDelimiter(_, "k"), type number}}) to extract Week Numbers from below column.

Durinng this step, I specify that column type should be number however, when I used Value.Type to check the column type it is showing me as text. 
Can anyone pls help me understnad how is it possible?
= Table.TransformColumns(#"Unpivoted Columns", {{"FcstWK", each Text.AfterDelimiter(_, "k"), type number}})

= Table.AddColumn(#"Extracted Text After Delimiter", "Custom", each Value.Type([FcstWK]))

FcstWK

PlannedQuantityWeek0

PlannedQuantityWeek1

PlannedQuantityWeek10

PlannedQuantityWeek11

PlannedQuantityWeek12

PlannedQuantityWeek2

PlannedQuantityWeek3

PlannedQuantityWeek4

PlannedQuantityWeek5

PlannedQuantityWeek6

PlannedQuantityWeek7

PlannedQuantityWeek8

PlannedQuantityWeek9

[PhilipTreacy · Accepted solution]

@Gauravshahin 

What seems to be happening is that the data is transformed to a number, but the type of the column remains as text, so the numbers are treated as text.  I can't tell you why it's doing this as it seems wrong.

Even if you do this

each Number.From(Text.AfterDelimiter(_, "k"))

the column is still an ANY type (ABC 123) rather than number.  Seems that PQ is treating the column type and the data in the column as different types.  A bug?  Or by design?  I don't know why you'd have a column of one type and the data within treated as a different type.

Phil

[Anonymous]

I know this is solved, but @PhilipTreacy I think it's the opposite: the column is being changed to type number, but the values are still type text, which is still funny because the function returns type any:

Text.AfterDelimiter(text as nullable text, delimiter as text, optional index as any) as any

I'd be curious to know what the result is of you checking the column type as opposed to the value type:

Type.TableColumn(tableType as type, column as text) as type

This will return the column type as opposed to the value type. Not that this solved your question...

I'd also be curious to know the result if you used Number.FromText instead of Number.From. People say that they are equivalent, but in this case, I suspect they are not exactly the same.

--Nate 

[PhilipTreacy · Accepted solution]

@Gauravshahin 

What seems to be happening is that the data is transformed to a number, but the type of the column remains as text, so the numbers are treated as text.  I can't tell you why it's doing this as it seems wrong.

Even if you do this

each Number.From(Text.AfterDelimiter(_, "k"))

the column is still an ANY type (ABC 123) rather than number.  Seems that PQ is treating the column type and the data in the column as different types.  A bug?  Or by design?  I don't know why you'd have a column of one type and the data within treated as a different type.

Phil

[Gauravshahin · Accepted solution]

Thank you for your reply @PhilipTreacy.
I have done the work around and it seems to be working for now.
Since we now know that, output from Text.AfterDelimiter(_, "k") will always be ANY, I nested it with Number.FromText and specify column type as Number.

= Table.TransformColumns(#"Unpivoted Columns", {{"FcstWK", each Number.FromText(Text.AfterDelimiter(_, "k")), type number}})

However, I am still unsure why Table.TransformColumns is not converting it to number.

Preview