Forum Discussion

MarcSim's avatar
MarcSim
New Member
2 years ago

Correct data in a calculated column

Hi there, 

 

I'm working with a column that has multiple key/value pairs which has inconsistant entries, i.e. not all rows have the same key/values. I have a calculated column based on this that extracts one of the pairs nicely, however there are occurances of spelling mistakes in the values. Is there a way to correct these spelling mistakes, or normalize the data, from the calculated column?

 

Here is an example of the original column:

And my calculated column looks like this:

And the values look like this:

I see there are a number of posts and tutorials about fixing data, but none seem to do this with a calculated column. Also, in visualizations, I'd like to use the fixed column to filter charts (as a drop down)  so I believe I'm unable to use a measure to achieve my intended result.

 

Any assistance is appreciated.

Cheers,

Marc

Need Help

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Data cleaning should be done as far upstram as is possible.  If it is not possible to make such spelling corrections in the source dataset, then one should do so in Power Query.  If you are amenable to my approach, then share some data to work with and in a spare column, show the expected result.

    • MarcSim's avatar
      MarcSim
      New Member
      2 years ago

      I appreciate your response. The data is coming from Azure Cost Management and the field in question is tagged with the data at time of charge. So you see there is no way to retrospectively fix the data at the source for past charges. As for Power Query, yes you can make changes there but you cannot work with a calculated column in Power Query can you?

       

      Cheers,

      Marc