missing data in table with no filters

[Anonymous]

I created a table report in Apr 2020 Desktop version, and reproduced still have same issue after upgrade to June 2020 Desktop version.

Report runs a SQL Server proc which loads a temp table and select from it at the last step for the result set.

In report table it shows some records missing, however rows are present in query editor

1. there is no visualization filter (selecting the table and filters from the view tab - shows 

Column1 is (All)

Column2 is (All)

Column3 is (All)

2. Also there is no report or page filter. 

I already selected refresh in query editor, but it did not fix the issue.

Could it be there are some duplicates? 

I am fairly new to Power BI.

Thank you,

[jdbuchanan71]

@Anonymous 

If you assign an ID in SQL do all the lines come in?  You could use ROW_NUMBER to do this, just add the line into the select from the temp table.

SELECT *
	,ROW_NUMBER() OVER ( ORDER BY <one of your columns in the temp table> ) rn
FROM #temp

Then you can pull the rn column into your visual to see that they all come in.  Make sure to set the Summarization to 'Don't summarize' on the rn column in Power BI.

[Anonymous]

 Looks like I have an issue in the datatype, but was not able to figure out. I am probably green in Desktop.

Data query looks good since ordering on LogDate which is datetime is correct and there are 171 records in the result set and looks good . also largest rn is 171.

but table report does not look good. (and generates way higher rn as per below.)

Table Report

[jdbuchanan71]

If you run the code directly in Management Studio which one does it match?  Sometimes the query editor does some strange caching.

[Anonymous]

It is the query editor that everything is correct.

Strangely in the table report for a few records the rn is much higher than in query editor (as per previous screenshot), eg: values 794 while the max rn is 171 in query editor. Also if sorting by rn desc the logdate appears not in order (as per screenshot).

thank you,

[jdbuchanan71]

So in SSMS you get no rn 794?  That is very strange, can you share your .pbix?

[Anonymous]

I noticed that by entering "don't summarize" under formatting for "rn" it removes the 794 and creates correctly in the table report and makes same as in Query Editor (which is what i wanted) . For this, i still had to add the "rn" as the column in the table, otherwise if I remove it and since the logDate has duplicates somehow it does not show all records. I still did not grasp the correct explanation ( quite green at Power BI) .

Thank you,

[jdbuchanan71]

When there are duplicates in a column and you put it in a matrix it group the duplicates under a single entry.  Think about it like this: if you had a table a sales information with customer #, order #, and sales.  You want to see the total amount sold to a customer you pull the customer number into the matrix and the sales amount into the values and it groups all the sales under each customer number on a single row even if the customer number appears multiple times in the table.

[Anonymous]

the result set returns SQL events which could be some duplicates at same time. I guess my only choice is to generate this "rn" (indexer or additional column to bring row unicity) and return rn in the result set. Please let me know if this is correct.

Thank you,

[jdbuchanan71 · Accepted solution]

Yes, you will need some sort of ID that lets PowerBI know that the row is unique.