cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mokoweb
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
friskel126
New Member

I've also got the wrong answers. I had 10782612 for International Sales and 9985368 for Sales. No idea how far off wrong I am. I did check the queries and neither had dates for 1999.

 

International Sales.JPGSales.JPG

friskel126
New Member

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.

janinellh
New Member

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)

 

 

Hi, How can you see the last row? I am trying to do manually. It is taking years.

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

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

abinashphuel
Advocate I
Advocate I

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

renvoort
New Member

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

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! 

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.

Ajithd
Helper I
Helper I

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!