topic Re: Alphanumeric column error in Power Query

Alphanumeric column error

pooramit4 — Wed, 03 Jul 2024 09:19:29 GMT

I am PBI beginer. I have a dataset in excel with Duration column as Alphanumeric. Below is the snapshot

When loading it to Power BI, getting error for YTD.

Is there any other way to write this YTD so that I do not get the error and the sheet is uploaded in proper manner.

Re: Alphanumeric column error

AlienSx — Wed, 03 Jul 2024 09:31:50 GMT

pooramit4 transform Duration column to text before loading.

Re: Alphanumeric column error

collinsg — Wed, 03 Jul 2024 11:34:57 GMT

Good 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.

Re: Alphanumeric column error

pooramit4 — Mon, 08 Jul 2024 08:39:23 GMT

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.

Re: Alphanumeric column error

pooramit4 — Mon, 08 Jul 2024 08:44:23 GMT

Thanks AlienSx for you support.

But its not working as the column has both dates in DDYYMM Format as 202305 & YTD format like YTD 05-22 i.e. YTD MM YY.

Is there any other way?

Re: Alphanumeric column error

foodd — Mon, 08 Jul 2024 08:47:49 GMT

Hello pooramit4 . To encourage Community Members to assist you with your question, please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share a file’s URL.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

Re: Alphanumeric column error

collinsg — Tue, 09 Jul 2024 07:56:16 GMT

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.