Incorrect Sums using folder to get data

[Anonymous]

Good Morning,

I feel like I am on a roll for asking questions. It seems this one budget document is giving me all sorts of issues. So basically I have an issue where the sum of all expenses is very incorrect. If I upload the files individually rather than through the folder it displays the correct sum. I am attaching a bunch of photos that describe the situation.

I can provide more screenshots if you need clarification. The first photo is just a sample if i uploaded things individually. The next set of photos is my actual working project that uses a folder to pull files

[Anonymous]

@Anonymous,

When you load individual files, Power Query automatically performs data conversion. In you case when iterating all files in a folder, make sure you change each column's data type after combining all the files. Try this out first and if the issue still persists, let me know.

[Anonymous]

@Anonymous 

Do you mean within the sample file? Or after everything? Because I didnt really edit the sample file.

-Michael

[Anonymous]

@Anonymous,

After each query

[Anonymous]

@Anonymous ,

Yes, In this photo you can see that I did the conversions after. Only to the date and the Cost 

[Anonymous]

@Anonymous,

What version of Power BI desktop are using? Please try to update to the latest version to see if it fixes your issue.

[Anonymous]

I am using the december release. I will try updating it and let you know. At my company sometimes it can take days to get permissions to install software onto the computer.

-Michael

[Anonymous]

@Anonymous,

I found a similar problem that has been resolved in this thread by checking "Ignore Privacy setting" in the Options, not sure if it helps you or not

[Anonymous]

@Anonymous ,

Thank you for finding that for me. unfortunately that did not fix the issue. Still getting very high numbers 12x as large as the actuals

-Michael

[Anonymous]

I am Still trying to resolve, if anyone has an idea let me know. I can provide more info

[Anonymous]

bump

[Anonymous]

Bump. I still cannot figure out this issue.

[Anonymous]

After further analyzing I noticed that Power Bi doesnt seem to be recognizing hidden rows from my folder. So it is essentially including rows that shouldnt be included. Thoughts on how to fix this?

[Anonymous]

@Anonymous,

Where do these hidden rows come frome?

[Anonymous]

@Anonymous 

Basically It's an expense report that gets reported monthly into a folder. The way they are reported "monthly" Is by updating the YTD expense report and hiding the rows for the previous months if that makes any sense. It appears that when these files are pulled into Power BI what I think is happening, is the rows that are hidden are shown and an incorrect sum results because I have a lot of repeat transactions when I shouldn't

-Michael 

[Anonymous]

Does the file name have any sort of context, like Month Name or Month Number?  If so, you could use that to filter out all the other data that does not fit in that month. 

That's just a high level guess though, is it possible to get two sample files? Or just some random actual data but correct on the file is named?  Just thinking out loud here....