Forum Discussion
Messy data handling in 1 column
Thanks Greg - added some extra details for clarification
Community ChampionI'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.
Thanks Hot Chilli
Will have a go at the M query one. I tried the duplicating columns but I think the problem is that whilst that would give me the dates, I need to find a way to bring in the other data too not get rid of them.
As you can see below, there is a user name, below that are dates and further below are times.
There is another column I'm trying to keep from this table which only has values on the date row.
There are other users as you scroll down and the table follows the same kind of format of User name then a whole bunch of dates and times. It then goes to the next user and a whole bunch of dates and times. Again, the value I'm trying to aggregate for users is only on the date row.
Imagine the pattern below repeats.
I'd like to be able to get a table that shows the User in 1 column, date in another and the value in the next.
I will try the from examples idea next.
Thank you!
- Ashish_Mathur
Super User
Hi,
Share data in a form that can be pasted in an Excel file. What do the numbers in column B represent? For the data that you paste here, show the expected result as well.
- HotChilli
I don't think the initial problem was fully descriptive.
Are you trying to get a table that looks like
User 01/07/2020 10
Jeff 11/08/2020 15
?
