Forum Discussion
Lab 1: Exercise 2 for DAT207X edX Course
I am having an small problem here specifically in Step 4, bullet point 2; "Select to combine (combined binaries) the content of those 4 files, leave the default settings, and click OK." and bullet point 3; "Filter the rows that are after December 31st, 1999". For me to be able to filter the dates, I need to change their type to "date" from text. When I do this type conversion, it does not process the conversion correctly and results in many erros (I suspect that the dd/mm/yyyy vs mm/dd/yyyy is the culprit).
Is this working as expected for the exercise (having to convert from text to date) or have I done something wrong or am missing?
Thanks in advance.
Hi paulphe,
In this scenario, an alternative solution is to change the locale (the date and number formats) before importing the data. :smileyhappy:
- Go to File > Options and settings > Options.
- Under Current file, select Regional Settings.
-
In the Locale box, select a proper locale.
-
Select OK.
Regards
Hi again...
Probably the problem appears after creating the new date column.
After splitting the date column (wrong date column) by the delimiter "/", you get 3 columns (day, month and year) but the default data type is whole number, then whenever you try to concatenate again these columns, a error message appears. It's because before creating a new column you need to convert these 3 columns from whole number to Text.
If the previous advice is not your case, try the next pbix file
6 Replies
Discussion. I have just started the Power BI course DAT207x. I already have some experience with Power BI but wanted to ensure I have all the basics. I have run into a challenge in Lab 1, exercise 2. Specifically this within Step 4...
- Filter the rows that are after December 31st, 1999."
Normally this is easy to accomplish as long as the data is type Date and that is where my problem is. In the query editor for the date column, it initially has a data type of text. I attempt to convert it to "date" format and after I accept the message that the type will be changed, I get mostly errors in the date result and I think I know why. It seems that the error conversions are due to the data being in mm/dd/yyyy format and the expectation it is in dd/mm/yyyy format. Maybe when I "Get Data" for the 4 CSV files something is incorrect (i accept the defaults as indicated). I have tried extracting the data column into new day, month and year columns and re-assembling it... still have an issue. I am sure it is not this complicated and am missing something basic.
Any help would be much appreciated.
Thanks in advance!
Hi again...
Probably the problem appears after creating the new date column.
After splitting the date column (wrong date column) by the delimiter "/", you get 3 columns (day, month and year) but the default data type is whole number, then whenever you try to concatenate again these columns, a error message appears. It's because before creating a new column you need to convert these 3 columns from whole number to Text.
If the previous advice is not your case, try the next pbix file
Hi paulphe
Yes, you're right, the problem is the structure of the date, it must be "dd/mm/yyyy" instead "mm/dd/yyyy" (needs to be changed). Don't worry the excercise is been correctly solved.
Great thanks for the confirmation.
Hi paulphe,
In this scenario, an alternative solution is to change the locale (the date and number formats) before importing the data. :smileyhappy:
- Go to File > Options and settings > Options.
- Under Current file, select Regional Settings.
-
In the Locale box, select a proper locale.
-
Select OK.
Regards
