Missing Relationship for some items in Fact Table

[MarkCBB]

Hello,

I have a MAIN database with a list of Unique ID's, I also have a Fact Table that contains the same ID's (Main=One, Fact=Many).

In the fact table there are some ID's that do not link to the list of MAIN ID's, How do I got about removing the ones that do not have a link to the MAIN database?
I am assuming that I need a helper column with lookup and filter the errors out, But I have no idea where to start.

Thanks

 

[mole999]

join on the records where facts exist

 

[Matt Allington]

How do I got about removing the ones that do not have a link to the MAIN database?
I am assuming that I need a helper column with lookup and filter the errors out, But I have no idea where to start.

Thanks

Power pivot is a reporting DB - you should only load data you need and do the cleansing before you load the data. I don't recommend dealing with this in power pivot.

Do you have sql as your data source? If so, you can write a simple SQL statement to load the data. Switch the fact table load from Table View to Query view. You then need to join the fact table to the main table before loading so it only loads valid records that exist in the main table. Something like this should work.

With temp as (
select ID

from tblMain
group by ID

)

select d.*
from tblFact as d
join temp on temp.ID = d.ID

the with statement creates a temp table of all the product IDs in your DB. This list of valid IDs is then joined to the fact table and only those IDs are loaded.

 

[MarkCBB]

Hello again Matt,

The challenge with this data is that there are 100 files that are uploaded daily (Short of automating the cleaning with VBA - I don't really want to do this as it will slow down the current cleaning process), that said I do understand your point about loading clean data.

I am using normal Excel files (From folder, each folder gets 100 files per day).

The only way that I can think of to do this in the Power Bi Desktop application is the in query editor, by adding that helper column that Looks up the Main table into the fact table, that way I can filter out the items that do not have a match,
Kinda like a VLOOKUP helper column and then filter out the #N/A's.

I know that I might seems like I am being difficult with this request, but the impact of adding code to clean the file takes the current cleaning process from 1 min to 10 mins. and each day the process will take a bit longer.

I will create example files during the day and post the links

 

[Matt Allington]

if that is your data source, then that is what you have to work with. How are you combining your excel files? I have a series of blogs on that. Here is the first one
http://exceleratorbi.com.au/combine-excel-workbooks-power-query-method-1

I suggest you do this.
1. Combine all the files of your data into a staging table (create the query but don't load it to the data model).
2. create a new query that does a merge on your main table with the above table. Select it to only load matching values. Load this one to the data model.

It probably wont be fast but it should work.

edit: This post is different to what you want, but it demos the merge feature http://exceleratorbi.com.au/power-query-as-an-audit-tool/

 

[MarkCBB]

Hi Matt,

Just to close this off, and as mentioned above by yourself and Mole999, I have updated the VBA code to go back and compare the previous data with the new data check for missing items, report them and then add them to the new file. This works and at least I can track it as well, then I use the Join to get rid of them to keep the data "Clean".

so in short problem solved, thank you once again for the help.