I am not sure why this is happening, so I will describe the whole process, and perhaps you can help me out, give some tips to debug the report or something.
The process
I have 4 separate folders, one for each customer.
inside each there are CSV files with data export. each day we make an export and add the file to the folder.
the csv files have an identical structure in terms of columns although they are from multiple instances.
i modify the query for each data source as follows:
initially when setting up the report, instead of loading the source i edit the query.
i combine the files in the folder. this is done by editing the query and clicking on the first column, there are 2 arrows pointing downward in right side of the first column. Power BI then combines the records from all files in this folder into one list
remove extra columns from the resulting table (by selecting the ones i want and Remove Other Columns) and only keep 3 (Date in format mm / dd / yyyy, Record ID, Test Outcome Name)
Record ID is only unique within the same folder. because it is for that customer. But another customer can have the same record IDs too, and those would be attributed to another customer based on the folder, as i am about to explain.
Then i select Record ID column and:
- remove blank rows
- remove duplicates
then i go to the table of this source and add a column Customer Name = 'XXX'
after all that i made a Union table and run reports off it, this way i can identify each customer Test Results based on the added column.
The problem
the tables for each customer have values in the Date column that are non existent in the CSV files. all our dates have only mm/dd/2017 but the resulting table has 4160 for the year!!! which is also shown in data slicer when i use the resulting date column from the union table. this is not for all values but only some....
also it looks like the Record ID values are also some of them are non existent in the CSV files.
The question
how do i troubleshoot this?
