cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mokoweb Frequent Visitor
Frequent Visitor

lab 1 exercise 2 row count incorrect

I followed all the steps in Lab 1, excercise 2 but my answers aren't correct in terms of the number of rows for the sales and international sales tables.

I got 211450 rows for international sales and 

         10196818 rows for the Sales table.

I've repeated the steps but got the same number of rows. 

After combining the CSV files, I removed the first row because it contained unrelated contents(.pydio info), could this have been the cause? Any help will be appreciated, thanks

11 REPLIES 11
Ajithd Frequent Visitor
Frequent Visitor

Re: lab 1 exercise 2 row count incorrect

i have got the same results. i could not filter the date changing the data type of Date column. I did remove as charachter values.  because when I tried to filter I got this error message. Then I filtered data just chacking any values less than 31/12/1999

DataFormat.Error: We couldn't parse the input provided as a Date value.
Details:
1/15/1999

renvoort Frequent Visitor
Frequent Visitor

Re: lab 1 exercise 2 row count incorrect

Similar over here. I get 841147 for the International Sales query and 10826515 for the combined Sales query. Not a clue what I did wrong.

bida Frequent Visitor
Frequent Visitor

Re: lab 1 exercise 2 row count incorrect

Hey buddy I had the exact same results as yours. Turns out I didn't filter the Date after appending, so you might want to check again on that. I think this is a commen mistake because it is a bit tricky as Power BI reload the data again including the data from 1999 !
If you go order your 10,826,515 rows in Sales table and 841,147 rows in International Sales you will find some rows with the 1999 date ! If you fix this you should find the correct answer! It fixed the problem for me so I hope it does the same for you. Good luck! 

Ajithd Frequent Visitor
Frequent Visitor

Re: lab 1 exercise 2 row count incorrect

Problem is I did the date filtering. When I did date filtering I had errors. It shows it cannot accept dates like 1/2/2000 etc. So I have to manualay remove dates with 1999.

abinashphuel Frequent Visitor
Frequent Visitor

Re: lab 1 exercise 2 row count incorrect

I think you have some mismatch while using the date filter. 

janinellh Frequent Visitor
Frequent Visitor

Re: lab 1 exercise 2 row count incorrect

I got 211,450 rows for International Sales and 10,196,818 rows for Sales which are incorrect answers for Lab1 Exercise 2. 

Here are the steps I did from No.4 onwards, red fonts represent the areas that I need the advice. Thanks!

 

4. Perform the following steps:

    • Name the query International Sales. (No problem)
    • Select to combine (combined binaries) the content of those 4 files, leave the default settings, and click OK(No problem)
    • Filter the rows that are after December 31st, 1999. (After I changed the data type to Date, most dates became errors, and I right click the column and selected 'Remove errors', and it worked. Then, I filtered the date after 31/12/1999. I am not sure whether the 'Remove errors' would also remove some dates which are after 31/12/1999. Need advise on this.)
    • Remove the Source.Name column. (No problem)

5. Edit the Sales Query from the US Sales. (I selected the append queries function on the Sales Query, not sure if this is correct)

 

6. Append the International Sales table to the Sales query from the US Sales (After appending, I filter the dates after 31/12/1999 on the International Sales Query, not sure if this step is needed?)

 

7. In the Sales query, add a custom column named Country Name which takes the value of the Country column when it is not null and the value of "USA" when the Country column is null. (No problem)

 

 

Promyse37 Frequent Visitor
Frequent Visitor

Re: lab 1 exercise 2 row count incorrect

What is the correct formula you used for the Country Name custom column?

Promyse37 Frequent Visitor
Frequent Visitor

Re: lab 1 exercise 2 row count incorrect

This formula worked perfectly for the Country Name custom column: if [Country]= null then "USA" else [Country]

friskel126 Frequent Visitor
Frequent Visitor

Re: lab 1 exercise 2 row count incorrect

I'm having issues as well. My answers came out to be 10782612 for International Sales (none of which the dates were 1999) and 9985368 for Sales (again none of which were from 1999). I'm so confused where my error is.