Forum Discussion
Messy data handling in 1 column
Hi everyone, I've got a file format I'm trying to handle which puts a whole bunch of different information into the 1 column.
It has date fields - eg 31/07/2018
It has user names
It has invoice numbers
There are other columns shown too which are necessary but I wanted to be able to split out the data in the aforementioned column into separate columns.
E.g. 1 column dates, another for users etc
Is there some sort of functions I can use to do this or DAX code that takes regular expressions (REGEX) to do this too?
thank you!
EDIT: Example image from Edit Query page attached - this shows how time fields and date fields are shown in the one column. I don't show it here but there are also user names in the same field that I'd like to parse. Hope this helps.
23 Replies
- Greg_Deckler
Community Champion
Need to see examples. Text preferred. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
- HotChilli
I'll chime in here if that's ok.
If you duplicate the column, you can then change the data type e.g. date or time. Powerbi will parse the column and return an error for non-compliant fields. Right-click on the column and 'Remove Errors'
The format of data provided looks reasonably straightforward so this should work. For more complex situations, try adding a column 'from examples' and give a few examples (i.e. on several different rows). Power Query will make a good effort at trying to get what you want. It doesn't always work but it's pretty good. You need to examine the M code generated as a sanity check.
- v-eachen-msft
Community Support
Hi Anonymous ,
Do you mean to get the result like the following image?
v-eachen-msft - yes - that's precisely what I'm trying to be able to do. How were you able to do that?
Hi v-eachen-msft - what was the DAX code you used to do what you had in your image?
