Hi,
I wonder if Power Query is capable of creating an index column (the ID column below far right) so that when new records are found/added, it will test whether the customer number exists, if not then the email, if not then name and phone together, and finally, return an existing index number if recognized, or adding a new if neither is recognized.
As a result, the ID column will grow and each ID number will be related to one customer alone, if recognized either by customer number, email, name and phone:
| Customer Number | Customer name | Phone | ID | |
| 10135000 | [email protected] | Benny Johnson | 4329463662 | 1 |
| 10031030 | [email protected] | Pete Madsen | 4346123746 | 2 |
| 10056030 | [email protected] | Pete Madsen | 4346123746 | 2 |
| 10164001 | [email protected] | Donna D. Nielsen | 4346272296 | 3 |
| 10258006 | [email protected] | Sanny Peterson | 4346414642 | 4 |
| 10258032 | [email protected] | Sanny Peterson | 4346414642 | 4 |
Is that possible at all? Or should a separate table be created in the power pivot data model?
Thanks for any possible hint!
Marianne
Hi again,
I believe I might moderate my "wish" a bit, in order not to be too restricted regarding possible solutions as an index might not be possible/the solution at all...
The need is basically to be able to determine the number of unique customers, and this is (or could be) defined by same name and phone. This person can then have several emails (in theory) and several customer numbers. This is relatively easy if concatenating name and phone and then removing duplicates (I don't take various entries of same phone number into consideration here).
But if I remove duplicates, then I loose all records of the various emails that could be attached to this person, and those emails are important in the relation to the table containing newsletter permissions.
So, what I am looking for is a way to attach all kinds of measures and attributes to unique customers (let's say defined by name and phone), without loosing any records.
Any hint/consideration is highly appreciated!
Thanks,
Marianne
To create a unique Index number by customer, you have to create a query from the source table, then:
-Add a custom column named Name&Phone, with the formula:
=[Name]&Text.From([Phone])
This will concatenate those 2 columns.
The next step is to use the Group By command from Transform tab, using the new column to group by, with Count Rows operation (Or Count Distinct Rows, or any operation, it's not really relevant, we se the grouping just to create a unique ID)
The only thing left to do for this query is to add an index column, starting from 1.
Here is how the query looks:
let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Number", Int64.Type}, {"email", type text}, {"Customer name", type text}, {"Phone", Int64.Type}, {"ID", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Name&Phone", each [Customer name]&Text.From([Phone])),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Name&Phone"}, {{"Count Records", each Table.RowCount(_), type number}}),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
#"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Name&Phone", "Count Records"})
in
#"Reordered Columns"
Now, load again the source table into another query, add the same new custom column named Name&Phone, with the formula:
=[Name]&Text.From([Phone]).
The next step is to merge the 2 queries, using the Name&Phone column as the key, with Left Outer as the Join Type.
Expand the index column and you will have the expected results.
Here is the query I tested for you, based on the data you posted:
let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Number", Int64.Type}, {"email", type text}, {"Customer name", type text}, {"Phone", Int64.Type}, {"ID", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Name&Phone", each [Customer name]&Text.From([Phone])),
#"Merged Queries" = Table.NestedJoin(#"Added Custom",{"Name&Phone"},Table3,{"Name&Phone"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Index"}, {"Index"})
in
#"Expanded NewColumn"
Hi,
Thanks you very much for your solution!
I only wonder, do I have to go through a merge of queries, every time I will have updates to the file?
Thanks,
Marianne
Only if you have more files, if you have changes to the same files, a simple refresh will be enough.
If you add new files every time, then you need to combine all files from that folder, this way the new files will be processed in the same way. Build the process according to your needs, there are solutions for each case.
