Forum Discussion

pooramit4's avatar
pooramit4
New Member
2 years ago
Solved

Alphanumeric column error

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 thi...
Need Help
Show and Tell
  • collinsg's avatar
    collinsg
    2 years ago

    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.

foodd's avatar
foodd
Icon for Community Champion rankCommunity Champion
2 years ago

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.