This topic contains 1 reply, has 1 voice, and was last updated by 
-
Posts
-
I’m wanting to use Power Query to fix up some poorly formatted fields in a csv file exported from a voip phone system, prior to the file being imported to a sales CRM. The plan is to do this daily until a better solution is found, such as a more direct sync via api.
There are two poorly formatted columns that I could use advice about …
- timestamp
- duration
Timestamp can contain any of these kinds of values:
- Today 4:32 PM
- Yesterday 3:52 PM
- Wed, Oct 5 1:22 PM
duration looks like these values which represent call length in hours:mins:seconds
- 0:00:11
- 0:02:32
- 0:14:45
- field can also be blank
I’ve been helped here before so while it’s been awhile I can dust off the learning and refamiliarize myself with M, just hoping to get pointed in a good direction.
The resulting better formatted file will then be imported into a CRM where each row will be associated with a user and eventually with a customer, based on the phone number called.
I
Forgot to add that the presence of ‘today’, ‘yesterday’ depends upon the date range specified. If the exported file is limited to a single day, then all of the timestamps will have ‘today’ in them.
It’s not practical to guarantee that the file will be exported every single day, so on some days it will be exported for a past range of dates. For example, if the person exporting forgets or is out, it might be days before we remember and then we’ll need to catch up for the missed days. The exported file would of course have a time/datestamp which might be useful in deducing what day ‘today’ is but that might not be reliable.
Perhaps the most reliable thing to do would be to always have a date range of >2 days, such that rows with ‘today’ and ‘yesterday’ can be ignored momentarily while real dates can then be sorted for most recent, and then ‘yesterday’ could be +1 day and ‘today’ +2 day after most recent.
The forum ‘Power Pivot’ is closed to new topics and replies.