Forum Discussion
Text.AfterDelimiter in transformation step did not change column type
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 |
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
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.
3 Replies
- PhilipTreacy
Super User
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
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.
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
