Forum Discussion
Alphanumeric column error
Good morning pooramit4 ,
To cater for your data being numeric and alphanumeric I've added an initial conversion to text - Text.From(_), I finish by converting text values back to numeric - Value.FromText(...). If you add the following step to your Power Query query your data it will produce an output of numeric dates. Replace #"Previous Step" with the name of your previous step.
= Table.TransformColumns(
#"Previous Step",
{{"Duration",
each if Text.StartsWith(Text.From(_),"YTD")
then Value.FromText("20" & Text.Range(_,7,2) & Text.Range(_,4,2))
else _, type text
}})This...
gives...
Hope this helps.
Thanks collinsg for your support.
But this code is too complicated for me.
In the image that I shared, the duration column has both numeric and alphanumeric dates like - the normal month date in DDYYMM Format as 202305 & YTD format like YTD 05-22 i.e. YTD MM YY.
Now to proceed with PBI, I have to normalise this column separately, I suppose.
As soon as I upload the sheet in PBI, the rows with YTD shows error.
How do I normalise this YTD. Or if I take it in different column, then whats the procedure.
Solution SageGood morning pooramit4 ,
To cater for your data being numeric and alphanumeric I've added an initial conversion to text - Text.From(_), I finish by converting text values back to numeric - Value.FromText(...). If you add the following step to your Power Query query your data it will produce an output of numeric dates. Replace #"Previous Step" with the name of your previous step.
= Table.TransformColumns(
#"Previous Step",
{{"Duration",
each if Text.StartsWith(Text.From(_),"YTD")
then Value.FromText("20" & Text.Range(_,7,2) & Text.Range(_,4,2))
else _, type text
}})
This...
gives...
Hope this helps.