# Is upgrading to Office 2013 Pro the answer to my memory problems?



## masplin (Mar 3, 2013)

Hi

I have 64-bit 16GB Ram machine that is constantly running out of memory on a big messy project I'm working on. The underlying data is coming form software written in Pascal and it a right told mess!!!  i am having to do an enormous amount of filter calculation which I assume where the problem lies. I have read that the new Office 2013 is a massive improvement specifically in this area. 

I was thinking I had to buy a much more powerful PC, but sounds like I should be trying the Office 2013 version first. I understand as an at home consultant this is going to be tricky as i have no interest in paying £10 a month for Office 365.  So the question is could I go down the volume licensing route mentioned in another post even though I am based in the UK?  Has anything changed on how a lowly individual can get hold of this?

Thanks for any advice

Mike


----------



## Andrew Poulsom (Mar 3, 2013)

What version of Excel do you currently have? In Excel 2010 the PowerPivot Add-In can handle enormous amounts of data. You have to have Office Professional Plus 2013 to get PowerPivot in Excel 2013.


----------



## masplin (Mar 3, 2013)

I have 2010 64-bit. I watch the memory climb to about 11GB before it keels over. I have a 500,000 row table doing filtered look up on another 500,000 row table becuase I cant get all the data in a single table.  The data is being replicated out of Pascal into SQL  so i'm linking directly to the SQL tables. If I exported them I could do the combination outside powerpivot, but then I lose the feed. I have failed to get the software company to provide all the data in a single table.

The question is how as a self-employed person in the UK can I get Office Pro plus?


----------



## Andrew Poulsom (Mar 3, 2013)

As far as I know the only way to get Office Professional Plus is through a Microsoft Volume License Agreement or Office 365.


----------



## masplin (Mar 3, 2013)

I found an article on Rob Collie's blog about being able ot get aslicence agreemnt ofr $30, but this was all US based so wondering if the same trick works in the UK? hoping someone in the Uk has found a solution.


----------



## miguel.escobar (Mar 4, 2013)

Ram is only used to store the Powerpivot data and to "hold" all the objects that you have in your worksheet. When you run your measures and play with your pivot tables, slicing and dicing, all that goes into place is the actual query time or as you can see on the status bar at the bottom its the OLAP calculation.

You can try the Office 2013 Pro Plus for free but there are ways to make your current model run faster like:
- sorting your data prior to the initial load with whatever column that you want
Alberto Ferrari : PowerPivot: improve the performances by sorting tables

There are other ways to improve it that were posted in Rob's blog. I'll try to find that post and publish the link here.

hope this helps


----------



## masplin (Mar 4, 2013)

Hi Miguel. Unfortuantely I have no control over how the data arrives with me. The majority of filtering i'm doing is on 2 fields, invoice numbers and unique cusomter identifier.  I'll try sorting by Invoice number as more of them.


----------



## miguel.escobar (Mar 4, 2013)

you should check this category
Performance « PowerPivotPro

if you happen to have columns in your data model that are not meaningful to you, please delete them.


----------



## masplin (Mar 4, 2013)

yup deleted everything I can


----------



## Jacob Barnett (Mar 4, 2013)

Sorry if this is a daft question but if your data is from 2 SQL tables can't you just UNION them together?


----------



## masplin (Mar 3, 2013)

Hi

I have 64-bit 16GB Ram machine that is constantly running out of memory on a big messy project I'm working on. The underlying data is coming form software written in Pascal and it a right told mess!!!  i am having to do an enormous amount of filter calculation which I assume where the problem lies. I have read that the new Office 2013 is a massive improvement specifically in this area. 

I was thinking I had to buy a much more powerful PC, but sounds like I should be trying the Office 2013 version first. I understand as an at home consultant this is going to be tricky as i have no interest in paying £10 a month for Office 365.  So the question is could I go down the volume licensing route mentioned in another post even though I am based in the UK?  Has anything changed on how a lowly individual can get hold of this?

Thanks for any advice

Mike


----------



## masplin (Mar 5, 2013)

Ah technical question!!!  Can I do that within the powerpivot import as I have no access ot the SQL tables and have failed miserably to get the sfotware company to get the data in the format I need. We are a small cusotmer of theirs and they have said they will do it, but who knows when.


----------



## Jacob Barnett (Mar 5, 2013)

If you have 2 tables called TableA and TableB your query could look something like:


```
select
columnA,
columnB,
columnC,
columnD

from dbo.TableA

Union

select
columnA,
columnB,
columnC,
columnD

from dbo.TableB
```

As long as the 2 queries have the same number of columns and each column has the same data then this construct should work. You could potentially introduce duplicates unintentional duplicates which you could avoid using two fields concatenated together as a key. Also worth looking up the difference between UNION and UNION ALL.

Hope this helps
Jacob


----------



## masplin (Mar 5, 2013)

Unfortunately what I have is one table with 6 bits of data I need and another table with 12 bits of different data. The common link between them is they both have invoice numbers. So I'm having to do a calculate values where invoice match type lookup to get the data from table 1 into table 2.


----------



## Jacob Barnett (Mar 5, 2013)

OK - so I take from this you are lacking the SQL know how to pull the 2 together. Depending on how your tables are set out this could well be extraordinarily simple!!

If you can post the table names and required columns I can write the query for you.

Jacob


----------



## masplin (Mar 5, 2013)

Hi Jacob

yes sorry I'm completely untrianed and making this up as I go along!!! All I have worked out is how to connect to the DB and select a prexisting table.  Does this mena I will lose all the work I've already done building measures based on the tabled pulled straight down?


----------



## Jacob Barnett (Mar 5, 2013)

Not necessarily in that your new joined table will have all the same columns that your previous one did just with the extra info from table 2 integrated.


----------



## masplin (Mar 5, 2013)

Hi Jacob

So the main table i'm using is called dbo.VW_InOutStock.  Columns are:
Depot
Sysdate
customer
CTSalut
CTfirstN
CTSurname
CTFullname
Name1
ADDR11
ADDR12
ADDR13
ADDR14
PCODE1
PHONE1
CTPHONE
CTMOBILE
CTEMAIL
Group
INVNUM
INVDATE
STCODE
PRDGRP
SUMCODE
DESCRIPN
SALESN
STOCKN
VATTYPE
VATPC
QUANTITY
SELLPRICE
LINETOTAL
COST
LINECOST


The 2nd table I need to add is called custom.report3full. I'm not sure this is an sql tablns i need are
RType
TType
Description
RegNum
DVMake
MOTDue
ServiceDue

This table also contains the InvNum and all the above fields are duplicated in the multiple lines that make up one invoice. so I have been using a calculate(values(2nd table column),filter(2nd table, 1st table[invnum]=2ndtable[invnum]) to get them inot the first table as new columns.

I'm guess I go to existing connections and go to "write an sql query"

one hitch the 2nd table is updated every day, but the first table I got done once for me and have never been able ot get thme ot update it. It therefore is shorted and ends on 31/1/2013.  Does this knacker up any union?

Thanks for your hlep

Mike


----------



## Jacob Barnett (Mar 5, 2013)

Mike, a Union is basically where you join two identically structured data sets 'vertically' (i.e. one on top of the other). What seems to be required here is a simple join - the fact that one of your tables is stale isn't an issue as we can use a left join to ensure that we get every thing from the main table but you will obviously get some blanks for records where there is no data in the second table. What I don't see is a common column but assuming its invoice number as you mentioned earlier a query could look like:


```
select

[COLOR=#574123]IO.Depot,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]Sysdate,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]customer,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]CTSalut,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]CTfirstN,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]CTSurname,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]CTFullname,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]Name1,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]ADDR11,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]ADDR12,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]ADDR13,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]ADDR14,
[/COLOR][COLOR=#574123]CU.RType,[/COLOR]
[COLOR=#574123]CU.TType,[/COLOR]
[COLOR=#574123]CU.Description,[/COLOR]
[COLOR=#574123]CU.RegNum[/COLOR][COLOR=#574123]
[/COLOR]
from [COLOR=#574123]dbo.VW_InOutStock IO
[/COLOR]left join dbo.[COLOR=#574123]custom.report3full CU on CU.invoicenumber = IO.invoicenumber [/COLOR]
```

Should be pretty obvious how the join itself works - the trick is aliasing the tables, I've used IO and CU but these could be anything. The aliases mean you can reference the second table.

Something like the above should give you the necessary single table!

Hope this helps.
Jacob


----------



## masplin (Mar 6, 2013)

ah excellent - you learn something everyday whoich makes it worth getting up!    Essentailly an invoice may contian up to 20 lines of transactions, but every line has the same INVNUM and the same data from table 2. So there is a 20 to 20 lookup and VALUES only works becuase the table retruned by the filter has the same value.  As long as this just takes the INVNUM form Table 1 and looks up on TABLE 2 and takes the first match it should work fine. Will give it a go. Thanks


----------



## masplin (Mar 11, 2013)

Before I could attempt this the software guys added the missing columns to the main table!!!  Now I have the bizarre situation of removing al lthe filtered lookups has actually made the thing run like treacle!!!! so I'm now on Excel 2013 and using only 1 500,000 row table and seem to be worse off than before!!!  Is there some clever way of cleaning Excel 2013 up as know there was some clever trick to force Powerpivot 2012 to sort of rebuild?


----------



## masplin (Mar 3, 2013)

Hi

I have 64-bit 16GB Ram machine that is constantly running out of memory on a big messy project I'm working on. The underlying data is coming form software written in Pascal and it a right told mess!!!  i am having to do an enormous amount of filter calculation which I assume where the problem lies. I have read that the new Office 2013 is a massive improvement specifically in this area. 

I was thinking I had to buy a much more powerful PC, but sounds like I should be trying the Office 2013 version first. I understand as an at home consultant this is going to be tricky as i have no interest in paying £10 a month for Office 365.  So the question is could I go down the volume licensing route mentioned in another post even though I am based in the UK?  Has anything changed on how a lowly individual can get hold of this?

Thanks for any advice

Mike


----------



## Jacob Barnett (Mar 11, 2013)

Apologies in that this is partially my fault - I was focused on getting you that single table I wasn't really thinking about the wider picture!

Fundamentally 1.5M records is a data set that PowerPivot should be able to handle with ease but when its say 20 columns wide then it runs into problems - have a look at Rob's post on this issue: Less Columns, More Rows = More Speed! « PowerPivotPro 

Your fact table (the 1.5m rows) needs to have as few columns as possible with the other information sat in separate table e.g. the only customer info you have in the fact table is customercode, this is then related to a customer table on the customer code that has one record per customer with all the customer data residing in this 'look-up' table.

Obviously to make this work you are going to have to get access to the data in a slightly different way which sounds like it could be problematic.


----------



## masplin (Mar 12, 2013)

Hi Jacob I obviosuly need some education on reorgansiing the data as best I can within my limitiations. Before I try and explain what i have I need to walk the dogs. However quick question.  Why does it take 15 minutes and 10GB of RAM for me to hide or unhide a sheet?  This makes no sense as sure this doesn't involve any calculations. Is this reasonable no matter how rubbish the data structure is?  I'm trying to work out if the problems are all in the workbook or something structural. I'm sure 2010 doesn't recalculate the whole workbook every time you make changes to the powerpivot sheet so is there an option to turn this off?

mike


----------



## masplin (Mar 12, 2013)

Ok so maybe i have gone on a bit of a wild goose chase with what i asked them to do for me. If you coudl give me some advice on the best way to arrange the data that would be appreciated.

So as background this data is all to do with car servicing. the table i have at the moment consists of individual invoice lines with each invoice containing up to 20 lines for parts etc.  There is no customer table as all the information is buried in the invoices. So in this table I have a list of non-unique invoice numbers and then say 10 columns of transaction data that is unique to that line i.e. a part and its price/quantity. Then I have maybe 10-15 more columns of invoice specific data such as customers name, address, car reg num etc so this data is duplicated say 20 times on a 20 line invoice.   A lot of the columns contain rubbish so I also have calculated columns to reformat and clean up postcodes/mobiles/email etc.  So this table is getting pretty wide.

To create a customer list I have concatenated surname and postcode to create a sort of unique identifier. I made pivot table of this calcualted field and then reimported it as a new table. I have then extracted using a filter function the customer static data form the big table. So again massive duplication of all this static data on both tables. 

So should I be trying to get the big table split so I have one table of 500,000 invoice lines but only the 10 columns that are changing i.e. not the static data for the invoice. Then a second table also with invoice number but only a single line with the static data maybe 100,000 rows.  My problem is that i know there are some duplicated invoice numbers so I wont be able to create a relationship between the transaction table and the static data table. This probably torpedoes this as an idea? 

Thanks for any suggestions

Mike


----------



## Jacob Barnett (Mar 12, 2013)

Double post.


----------



## Jacob Barnett (Mar 12, 2013)

Mike, you've basically got the right idea. 

Based on the info you've provided so far I would bet that there is a customer table you just haven't seen it - the main table you are looking at is a 'view' which has probably been setup to summarise a bunch of other tables in a easier to digest way however it is extremely unlikely that the data was stored like this in the first place. Its worth asking again - if you get no joy its actually possible to create a customer table from the fact table using SQL.

If INVNUM is going to be the common field you should create a separate table of invoice numbers to use as an intermediate table to link the other two to (i.e when you drag invoice number into a pivot you would use the one in this table). The SQL would look something like:


```
select
   distinct(INVNUM)
from dbo.[COLOR=#333333]VW_InOutStock[/COLOR]
```

Doing your calculated columns in the SQL will also help - there are basically a load of functions in SQL that you can use to manipulate text strings etc that are pretty simple and this could have a HUGE impact on the performance of your model!

Hope this helps somewhat.............
Jacob


----------



## Laurent C (Mar 13, 2013)

Based on your description of the data you import, I suggest you simple SQL queries to reduce the amount of data you import and have a better data model.

You might want to have a Customers table, that includes any field that are specific to customers: fields whose value does not depend on date, invoice, or any other entity ...

If a customer only ever has one address then you might want to include it in the table. If it depends on the order then you might want to have this information with the invoiced table.

If you do not need to have the address in your model, do not import it. Same thing for first name, last name, ... (full name might be ok for your requirements)

If I understood correctly the second table represents the item list corresponding to an invoice.

Whereas a pure star-schema is usually recommended, in that case, you might want to go for a more straightforward model:
Invoice Details 
 -> Invoice 
Invoice
 -> Customer
 -> Date
Customer

As mentioned above, the SQL for getting the data in the required format will follow this simple pattern:
SELECT DISTINCT
 CTfirstN
 , CTSurname
 ...
FROM YourTable

This shoudl greatly simplify your calculations as well. Most measures only require very simple DAX expressions when the data model is right.


----------



## masplin (Mar 15, 2013)

Hi Laurent

I know nothing about SQL!!!  OK so seems you are suggestion instead of dumping the whole of this InoutStock table in one query I can do 2 queries, one that lists all 500k rows of transactions, but without all the duplicated customer details columns.  Then I do a query to extract just the unique invnum details into a 2nd table and create a relationship between the InvNum on the transaction table and Invnum on the new table (which might only have 100,000 rows).

I'm a little unclear on the query as what I need is a new table with InvNum (unique), surname, address, phone etc all of which may be duplicated (customer came in twice). I'm assuming the query above gives a list of unique InvNum, but how do I get the rest of the data for that InvNum?  Do I just add the other columns like this


select    distinct(INVNUM)
CTFirstN
CTSurname
from dbo.VW_InOutStock

One small hitch is I'm told there are duplicated invNum used for different transactions. 
 The only way be sure it is actually unique is to join the InvNum with the Date. is it
 possible to make the distinct (InvNum) a distinct combination and then get the other fields? 

My big problem is there is no such concept as customer and the data is patchy i.e. sometimes no surname or address. Does the 2nd query
 SELECT DISTINCT find a distinct entry for all the fields you enter below  i.e. distinct combination? If I had all surnames and post codes I would say a unique customer is postcode-surname, but i don't.

Thanks again

mike


----------



## Laurent C (Mar 15, 2013)

What I suggested was to have 3 tables in your model: Customer, Invoice, Invoice Details.

As Jacob mentioned, there is probably a Customer table somewhere, so check if you can access it. Ask your IT if need be.

Note that IT departments are used to provide views, with repeated information if need be, so that business users do not have to JOIN tables in queries.

Since you use PowerPivot, you do not need to write JOIN queries, and you do not need to have a single view that pulls every piece of information. Don't be afraid to ask your IT department if they can provide these 3 views. (Note their DB schema probably follows this schema)

Otherwise, some SQL will be required (assuming you access these data through a database system.)

You do not need to know much SQL though.

You can use a DISTINCT query to get one row per customer:
SELECT DISTINCT
 CustomerIdentifier
 , CTFirstN
 , CTSurname
 ...
FROM dbo.VW_InOutStock

At the risk of repeating myself, if you do not need some columns, then do not import them.

Do not add the invoice number to that query!! The goal is to have one row per customer. You should have a field that uniquely identifies a customer in there. If there is no technically available key, then you will have to make up one. What to take is what most appropriate for your data. If there are rows where the customer is unknown, then let it be unknown.

For the Invoice table, do not import first name and last name: just import the identifier for your customer.

The Invoice number should go into the invoice table. If you need to concatenate the date with the invoice number to build a key, then do it. You can do it as a calculated column by the way.

As you fear, a SELECT DISTINCT query, will indeed list all combinations. That means, for example, if the phone number for a customer was not filled then such a query, with the telephone number would return two row for this customer.

Some cleansing would be required. The required SQL is no witchcraft, but might be intimidating if you have never touched SQL before.

Letting your IT department deliver cleansed data should be the first choice. This will require you to provide some detailed input about what you want to get, of course. If they cannot, then see if you can hire a qualified consultant for a short-term contract.

If none is possible, then you might have to do it by yourself, and learn some SQL.


----------



## masplin (Mar 15, 2013)

Hi Laurent. The big issue is there is no IT department!!!! There is also no unique customer identifier so I make one postcode&surname, however if no postcode then surane&carregnum, if no surname postcode&carregnum etc etc.   Basically I created a pivot table of these unqiue codes, copied nto a table and then reimported ot powerivot then extracted the detaials by filtering on the unique code. all very memory intensive.  Sounds like i'm gonig to need ot leanr some SQL to able ot cretae a uniquecusotmer identifier that can then be pulled down into a customer table and an invoice table!!!


----------



## Laurent C (Mar 15, 2013)

Sounds so.

Since we had started talking about SQL, I assumed  you pulled your data from a server, with IT guys managing it. 

Remember that calculated columns are only calculated upon updating your model. Once updated, they do not require more work than if they had been imported, even when they are very complex. So, if it just about adding one column key (which you will need anyway), then PP *might *be enough.

However, since neither PowerPivot or Excel is made for extensive data cleansing, you may want to use Access or SQL Express if you require more than that.


----------



## masplin (Mar 3, 2013)

Hi

I have 64-bit 16GB Ram machine that is constantly running out of memory on a big messy project I'm working on. The underlying data is coming form software written in Pascal and it a right told mess!!!  i am having to do an enormous amount of filter calculation which I assume where the problem lies. I have read that the new Office 2013 is a massive improvement specifically in this area. 

I was thinking I had to buy a much more powerful PC, but sounds like I should be trying the Office 2013 version first. I understand as an at home consultant this is going to be tricky as i have no interest in paying £10 a month for Office 365.  So the question is could I go down the volume licensing route mentioned in another post even though I am based in the UK?  Has anything changed on how a lowly individual can get hold of this?

Thanks for any advice

Mike


----------



## masplin (Mar 31, 2013)

Hi Guys. Sorry been on holiday hence reason for silence. I don't want to waste too much of your time but could you give me a steer on how big a job i'm looking at.  My big problem is there is no "customerIdentifier" and I'm strugglingto think of how to make one.  However couple of strategic questions.  I did the select distinct for the invoice static data and below is a typical output.


InvNum RegNum Salut Surname ADDR1 ADDR2 ADDR3 Postcode Mobile Email 123456 OE08 DYO Mrs Smith Flat 15, 1 Jones Ave Stoke ST1 4RQ 07901 533985 sally.smith@gmail.com 123457 OE08 DYO Mr Smith Flat 15 1 Jones Ave Stoke ST1 4RQ 07977 659867 tim.smith@gmail.com 123458 OE08 DYO , Smith Flat 15, 1 Jones Ave Stoke ST14RQ 7901533985 sally.smith@gmail.com 123459 OE08 DYO Mrs ? ,, Stoke ST1 4RQ 07901 533985 sally.smith.gmail.com 

<tbody>

</tbody>
As you can see data is sometimes incomplete (blanks or spacial characters), invalid (no @ in email) or just different (use of comma or spaces).  We do not always have the car reg either.  It is obvious to the human eye this is one household with 2 people bringing in the same vehicle.  So I have 2 strategic questions:

I there a straight forward way of validating data before importing i.e. length of field, contains certain characters, only contains letter or numbers or letters&numbers but not special characters?  Currently I import as is and create "clean" calculated column for every field i.e. doubling the number of columns. 
It seems that the logic to interpret this data as one household of 2 unique customers would be extremely hard to code.  Maybe this is easier to achieve in SQL than I imagine? 
Clearly we need to improve the quality of the data capture, but we have no control over the level of validation at the input stage.  We are looking at changing system by the end of the year, but in the short term I have to work with what we've got.  The question is what sort of level of investment in time will I need to make to firstly learn the skills and implement a solution, and how good could it realisitcally be if the raw data is as above? Basically do I accept I see 4 unique customers with the downside of undervlauing them and also overmarketing to them.

Thanks for any advice before I jump in at the deep end

Mike


----------



## prez02 (Mar 31, 2013)

Most SQL-dialects have text functions like left(), right(), substring(), length() you could use to cleanup column values or combine them, and case to check whether certain criteria are met. 

It is not that difficult, instead of 

Select table.column 

you have to write eg.

Select left(table.column,4) as Firstfourletters.

 I use it a lot instead of calculated columns in Powerpivot. 

We have a lot od duplicate customers in our company database as well. For us, the solution was too manually maintain a separate database in Excel, which we use for malings, but that required some work done manually, even after using an algorithm to detect duplicates.  So it really depends on what you want to do with the data, and whether it is worth the effort?


----------



## masplin (Apr 1, 2013)

So SQL has similar functions to excel. Is there any thing to ask "is this a letter" as opposed to "is this text"  so i can test if a cell contains "?" or "/" for example? Can you right an if statemnet e.g. select if(len(table.column)=7,table.column,blank()) ish?

Yes its really for marketing purposes so similar to yours.  If the records were complete and just duplicates it would be relatively stragiht forward. the problem is i have no idea if a customer has visited us 6 timnes what to expect in terms of static data.  My 2 objectives are:
1. understand the "value" of a customer so that means including all their transactions
2. Market to them based on their previous transactions and where possible have access to mailing, email and sms (so combining if entries are not complete)

Failing to idenfiy unique customers means we will send conflicting marketing messages multiple times to the same person.  So probably we need to maintain a unique customer DB as you have outside of the system.  Maybe that is actually easier.

thanks

Mike


----------



## prez02 (Apr 1, 2013)

A basic SQL statement tends to have three parts: 
(1) SELECT columns (2) FROM table(s) (3) WHERE filter condition(s). 

There is no if-clause, but a case function which lets you do the same.

SELECT 
table.column,
CASE table.column      
   WHEN len(table.column) = 7 
   THEN "Hooray"    
    ELSE "Boo"
End,
table.column,
table column,
....
FROM table
WHERE year(table.date)>2010

That said, I am not a SQL experts (and this is a Excel/Powerpivot forum ) but if you look around on the web, there is a lot of information about SQL around. Just beware, there a different dialects like MySQL, T-SQL etc, which differ slightly. The joining of two or more tables in the FROM clause is what is more difficult to grasp, just ignore it, you do not have to use it in your case.

I would try and base my analysis around the car registration number. Every car has to have one, it is unique, everybody is willing to give this information to you, it is relatively short, which could mean less mistakes and a greater likelyhood that it will captured all the time, or at least the easiest you could get people to capture. 

An email adress could also be useful, but not eveverybody has one, or is willing to give it to you. Then you could check what information you have about the owner(s).  they have moved house, or maybe several people living there.

You could to this by just creating a table that ownly consists of the different or distinct regnums, and then create a relationship to your other table, so you could see all the information you have about one car.  The only thing you would miss this way, is people changing cars, or having two,. It is really about to have to decide what is worse, writing two letters to one and the same customer or writing none at all.
Hope this helps a bit,
Carsten


----------



## masplin (Apr 1, 2013)

Thanks Carsten. I'm going ot do a bit of research into the data to find out what sort of quality I have for different fields. As you say regnuym woudl be pretty perfect if highly complete.  Thnask for your help

Mike


----------



## masplin (Apr 6, 2013)

stupid question deleted.


----------



## masplin (Apr 6, 2013)

I'm obviously quite stupid as cannot work out how to get data from table 1 to table 3. as suggested I have removed the static data from the transactions and created an invoice table containing it. This reduces my columns enormously and removes lots of duplicated data. I assume I am doing what was suggested.

Table 1: Multiple transactions under unique Invoice numbers (500k) (each invoice may have several transactions)
Table 2: Unique invoices each with a customer code (150k) (each customer code may have several invoices)
Table 3: Unique Customer code (80k)

Table 1 has a relationship with table 2 via INVNUM (unique in table 2)
Table2 has a relationship to Table3 via "Customer Code" (unique in Table 3)

For each customer in Table 3 I want a calculated column for count of transactions, but cannot work out the syntax. Each customer code is related to several INVNUM which in turn are related to several transactions.

Previously I had the unique customer code on both transaction and customer table so could filter like this


```
=CALCULATE(COUNTROWS(Transactions),filter(Transactions,Transactions[Customer Code]=Customers[Customer Code] 
)
```

Now the link is through the Invoice table and i am mystified. I've tried putting RELATED in every combination I can think of with no luck. thanks for any advice.

Mike


----------

