Reply
Highlighted
Frequent Visitor
Posts: 4
Registered: ‎06-13-2016
Accepted Solution

Lab 1 Exercise 2 - Changing Data Type to Date

Hi All,

 

  1. Perform the following steps:
    • Name the query International Sales.
    • Select to combine (combined binaries) the content of those 4 files.
    • Filter the rows that come from the header of the CSV files. (Hint: One way to do this is to filter out the Country column from records containing “Country”).
    • Filter the rows that are after December 31st, 1999.

I tried to change the data type for the "Date" Column to date but there are error encountered. 

 

DataFormat.Error: We couldn't parse the input provided as a Date value. 

Details: 1/15/1999

 

These happen most of the value in the Date Column 


Accepted Solutions
Established Member
Posts: 136
Registered: ‎11-28-2015

Re: Lab 1 Exercise 2 - Changing Data Type to Date

I was hitting the same error DataFormat.Error on the Date column. I got around it by using the "Remove Errors" function. Select the International Sales query, select the Date column, the click on Remove Errors (circled below). After doing this, you can apply the filter and continue.

 

Lab1DateError.PNG

 

Thanks,
Sam Lester (MSFT)

View solution in original post


All Replies
Established Member
Posts: 136
Registered: ‎11-28-2015

Re: Lab 1 Exercise 2 - Changing Data Type to Date

I was hitting the same error DataFormat.Error on the Date column. I got around it by using the "Remove Errors" function. Select the International Sales query, select the Date column, the click on Remove Errors (circled below). After doing this, you can apply the filter and continue.

 

Lab1DateError.PNG

 

Thanks,
Sam Lester (MSFT)

Frequent Visitor
Posts: 5
Registered: ‎06-14-2016

Re: Lab 1 Exercise 2 - Changing Data Type to Date

Ask for help Smiley Frustrated

 

I have problems of:

9.Explore the imported data in the Data View. (a) Where is the button of Data, and (b) What to View?

10.Hide the International Sales table from report view. (a) What is the purpose of hidding the International Sales from report veiw?

 

For the Questions:

Lab Questions

 

Answer the following question by reviewing the imported data in Power BI Desktop. (Hint: Use the Data view).

I cannot understand what is the questions asking for, since I cannot locate the information from steps 9 and 10?

How many rows were imported from the International Sales query?

How many rows were imported from the Sales query?

 

Thank you for your help to allow me move on.

 

Cheers!

Louis Chung

Established Member
Posts: 136
Registered: ‎11-28-2015

Re: Lab 1 Exercise 2 - Changing Data Type to Date

Hi Louis, the overall process for this lab is to load the US Sales, then load the 4 international sales csv files from a folder and perform some translation on them (steps 3-7). Step 8 is to load the data, which takes a little bit of time. After that, the data tab is on the left hand side, circled in the picture. At the bottom of the data tab, also circled in the picture, is the number of rows that are included. I did not perform the filter/transform steps in the lab for this screenshot, so the number is incorrect, but you can get the values by doing the couple steps in the lab.

 

DataTab.PNG

 

Thanks,
Sam Lester (MSFT)

Established Member
Posts: 136
Registered: ‎11-28-2015

Re: Lab 1 Exercise 2 - Changing Data Type to Date

[ Edited ]

As for hiding the international view, I believe the goal is to show that it is possible to hide various data sets from the visual display. You may want to do this if a specific data set is used in calculations/measures, but you want the user only to use the calculated values as opposed to the original data, in which case you could hide the original data. I don't believe there is anything in the lab exercises that depend on this data set being hidden.

 

Also, if this answers the question, can you please mark the question as answered so others can easily find assistance? Also, please provide kudos to those who have helped.

 

Thanks,
Sam Lester (MSFT)

Frequent Visitor
Posts: 4
Registered: ‎06-13-2016

Re: Lab 1 Exercise 2 - Changing Data Type to Date

Hi Sam, 

 

Thanks for the solution. Works perfectly for me.

Frequent Visitor
Posts: 5
Registered: ‎06-14-2016

Re: Lab 1 Exercise 2 - Changing Data Type to Date

Thanks Sam,

 

Maybe I'd have passed a wrong indication for asking answers to the Lab Exs.

 

Since I am not sure the "import" meaning, is it either refer to the no. of rows of importing from those 4 CSV files, OR, after rounds of filterings, such as elimimated the errors, or Appended with other tables.

 

So, without clear identificatin of the "import", I entered the answers of completed step 8,

 

And that was wrong because the correct answers for both questions were much more than the value which I answered.

 

Thus, I think the "import", should referring to the situation of step 2 and part of step 3.

 

Although I got the wrong answers, I cannot know the correct step and reason of why I was wrong.

 

Thanks for your help to resolve my query.

 

Louis

Frequent Visitor
Posts: 5
Registered: ‎06-14-2016

Re: Lab 1 Exercise 2 - Changing Data Type to Date

 

Without reasons of model answers.Without reasons of model answers.

 

Established Member
Posts: 136
Registered: ‎11-28-2015

Re: Lab 1 Exercise 2 - Changing Data Type to Date

Hi Louis, I agree with the confusion. The word Import is used in several spots, some incorrectly, and several terms are a bit confusing if you haven't spent much time in the product.

 

The overall strategy in many of these labs is to:

 

Click "Get Data" and connect to some data source (Access, Excel, Folder, etc.)

Before clicking "Load" to bring the data into Power BI, click on "Edit". In here, you merge queries, add filters, remove errors, etc. You can do each of these steps after loading, but I found it much easier to do them in the Edit area prior to loading.

Once those are complete, then click Load to bring the altered data into Power BI.

At this point, you'll see the three icons on the left representing the designer (where you add visuals), the data pane (where you preview the data), and the relationship tabs (if you have data with relationships).

Nearly all of the lab questions want you to click on the Data tab after importing the data, select the appropriate query on the left (ex: Sales), and view the row count at the bottom. The screenshot I included above in this thread shows this final stage, but I didn't do a very good job of outlining the overall process.

 

Does this help or is it still unclear?

 

Thanks,
Sam Lester (MSFT)

 

Established Member
Posts: 136
Registered: ‎11-28-2015

Re: Lab 1 Exercise 2 - Changing Data Type to Date

Also, this thread started with a different topic, which has now been marked as answered.

 

Louis, do you have permission to branch the thread in the thread settings/options? We do this a fair amount in the MSDN forums, but I'm not sure yet if it is possible here. I was curious if you have permission to do so as the person who posted the thread. 

 

Thanks,
Sam Lester (MSFT)