Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Suppose I have start and end dates in various formats that was imported from sales force. Should this data be formated in Excel before formatting or done through the Query editor? If so where can I find information about this?
Start date | End Date
2015-01-08 | 31/07/2016
30/13/2017 | 31/07/2016
2015-01-08 | 2018-03-13
I would like to have the x-axis in terms of weeks (ie. Week 1, Week 2, etc.) for various rows.
Thanks,
Hi @Nusc,
As Ross73312 said, you could clean the data in query editor.
If you have solved your problem, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you still need help, please share some data sample and your desired output so that we could help further on it.
Best Regards,
Cherry
Power Query has the ability to convert this data with your help. The best practise would be to understand how the data is stored in the source and figure out the most robust method to get that converted into a Date format in Power Query.
As your data is going to be a Start Date and End Date, you'll want to have Power Query split this data into 2 columns to eventually store in the Date Format.
Once you have the dates in the right columns, now you can concentrate on correcting the data. One avoidable pitfall will be if you have DD/MM/YYYY data mixed with MM/DD/YYYY data. You'll have no idea which is which, so you'll have to assume its one or the other for your process.
You can attempt to create a new column and make use of Date.From to see if it can convert the Text data for you. Since this will happen row by row, its possible this might work first time. Should that fail you could instead add an earlier step of converting the Text under certain circumstances into a staging column. This might be something like "Are the first 4 characters (Text.Start) a number? If so, this must be YYYY-MM-DD, i'll then convert that into a standard format (i.e. "DD/MM/YYYY"). You'd only need to write this for the formats that are causing you hassles.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
68 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |