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.
Solution SageGood day pooramit4,
As AlienSx suggests it would be preferable to prepare the data upstream. If you have to deal with the data in Power Query you can transform the Duration column as follows,
- Check each entry to see if it starts with YTD.
- Transform the entry if it starts with YTD.
Here is sample code.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjYwVdJRCijKTylNLsnMz1OK1aGeeGSIi4KBqa6R0eCUigUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Duration = _t, Area = _t]),
#"Transform YTD" = Table.TransformColumns(Source, {{"Duration", each if Text.StartsWith(_,"YTD") then "20" & Text.Range(_,7,2) & Text.Range(_,4,2) else _, type text}})
in
#"Transform YTD"
giving...
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.
- collinsg
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.
