Hi Alison,

I would create a separate table with your Type information that contains the Contract No., Customer No. and Type data. You could do this by duplicating the Source Data Table and stripping out what you don't need. Then Merge the tables matching the columns in the Type table to bring in the Type column with a left outer join.

Mynda

I've done this by using Access to create a query ("PQSource") that will act as the source in Power Query

The original data table is linked to the ContractUpdate table (this contains contracts where no type exists at all for any version)

Using an iif statement in the query - the Type is updated either with the Type from the original data table or the Type from the ContractUpdate table.

I've then imported the PQSource into Power Query.  Created another query related to this containing just Contract and Type.  I've then merged the two queries together.   This seems to work.

Is there a way to skip the access part and do this all in Power Query?

You lost me with the 'original data table' as I don't know whether this is in Access or PQ. You then mention an if statement, but again I don't know if this is in Access or PQ because you then talk about importing the PQSource into PQ, so it implies these steps are done in Access. The process flow isn't clear and your file attached doesn't provide any clarification either.

That said, ideally you should have your contract types in a dim table, not in the fact table at all. Assuming the ContractUpdate table is your fact table.

Mynda

The original data source = the original file I sent.  This I put in access and linked it to the table “ContractUpdate”.  The join was on contract no and it returns all the rows from Original Data

the iif statement  is done in access to create a query called PQSource - this is imported into Power Query.

the Access part is simply updating the original data source with a Type where no Type existed at all for any version ie.  if Type is blank in original data source use [ContractUpdate.[Type] otherwise use [Original data].[Type]

can this Access part be done in Power Query

Yes. The attached file shows how you can use PQ to assign the Type to all rows for a contract based on the data in the Type column, but like I said, I wouldn't bring Type into the Contract Fact table, I'd load it to Power Pivot via Power Query as a dim table.

Thanks but this doesn't appear to do the Access part.   The ContractUpdate table includes all the contracts that do not have a Type referenced in the original source file.  The file you've sent hasn't updated any of those contracts - they are all still blank.

I'm updating the type column in the original data because I want to filter on this column before I take it into Power Pivot - is that the wrong way to do it?

The file I attached achieved your desired result in your original question and shows how to do a merge to bring in the Type information, rather than using an if statement. The technique applies whether the type information comes from the 'Original Data' and or another table like the 'ContractUpdate' table, or both.

Only now are you telling me that you want to filter out data based on the Type column before bringing it into Power Pivot, so the dim table I suggested originally isn't the way to go because we always want to avoid bringing data to our model that we don't need. In other words, yes update the column type and then filter in Power Query before loading to Power Pivot. If you want to do the merge for the Type data in Access you can, but equally it can be done in Power Query too. It's up to you and one way isn't more efficient than the other.

Thank you.   I'd rather not do it in Access if I can do it in one process in Power Query

I've tried doing a 2nd merge with the update type table but the data is in different columns?  How do I get it all into one Type column

Well, if there are no conflicts i.e. rows with Types in both columns, I'd merge the columns, otherwise you could use and if statement to create a 3rd Type column that brings in the Type from one column if the other column is blank. Then you can delete the extra Type columns.