Auto update data models from raw files to lookup tables

%3CLINGO-SUB%20id%3D%22lingo-sub-1680499%22%20slang%3D%22en-US%22%3EAuto%20update%20data%20models%20from%20raw%20files%20to%20lookup%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1680499%22%20slang%3D%22en-US%22%3EI%20have%20data%20models%20linked%20such%20that%20the%20raw%20sales%20file%20is%20linked%20with%20master%20product%20lookup%20tables%20and%20region%20lookup%20tables.%3CBR%20%2F%3E%3CBR%20%2F%3EBut%20many%20a%20times%20it%20happens%20that%20there%20are%20product%20values%20or%20codes%20which%20are%20not%20yet%20entered%20in%20master%20product%20lookup%20table%20but%20are%20present%20in%20the%20raw%20sales%20file.%20Is%20there%20any%20way%20that%20i%20can%20pass%20these%20new%20values%20to%20the%20product%20lookup%20table%20automatically%20for%20future%20references%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EEg%3A%20product%20code%20links%20product%20sale%20data%20from%20raw%20sales%20file%20with%20product%20details%20in%20the%20master%20product%20lookup%20file.%20In%20case%20the%20raw%20sales%20file%20throws%20a%20new%20code%20say%20123%20which%20is%20not%20yet%20present%20in%20my%20lookup%20table%2C%20is%20there%20a%20way%20i%20can%20automatically%20pass%20this%20data%20and%20related%20data%20from%20sales%20file%20to%20lookup%20table.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1680499%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1686812%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20update%20data%20models%20from%20raw%20files%20to%20lookup%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1686812%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F797862%22%20target%3D%22_blank%22%3E%40ShijuC3%3C%2FA%3E%26nbsp%3BWithout%20seeing%20your%20data%20model%2C%20I%20can't%20help%20wondering%20where%20a%20new%20product%20code%20in%20the%20raw%20sales%20data%20comes%20from%2C%20if%20the%20code%20has%20not%20been%20created%20in%20some%20master%20product%20list%20already.%20But%2C%20I%20take%20your%20word%20for%20it%20that%20you%20still%20have%20a%20need%20to%20add%20new%20products%20to%20your%20%22own%22%20master%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20Power%20Query%2C%20you%20could%20easily%20identify%20product%20codes%20in%20the%20sales%20table%20that%20are%20%3CSTRONG%3Enot%3C%2FSTRONG%3E%20in%20the%20master%20table%20by%20merging%20two%20queries%20with%20an%20%22anti%20join%22%20(left%20or%20right%2C%20depending%20on%20which%20query%20you%20select%20fist)%20and%20remove%20duplicates.%20This%20will%20then%20create%20a%20table%20of%20all%20new%20products%20that%20you%20need%20to%20copy%20into%20your%20master%20table%20and%20complete%20with%20whatever%20product%20specific%20details%20you%20want%20to%20store%20in%20it.%20(This%20is%20similar%20to%20performing%20e.g.%20a%20VLOOKUP%20of%20the%20product%20code%20in%20the%20sales%20table%20against%20existing%20product%20codes%20in%20the%20master%20and%20then%20filtering%20all%20the%20%22%23NA%22%20to%20find%20the%20records%20that%20have%20no%20match).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnce%20you%20have%20added%20the%20new%20products%20to%20the%20master%2C%20refresh%20all%20queries%20and%20the%20%22new%20product%22%20table%20should%20be%20empty%20and%20whatever%20lookups%20you%20had%20created%20to%20the%20master%20table%20should%20now%20include%20the%20relevant%20details%20of%20all%20new%20products.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor
I have data models linked such that the raw sales file is linked with master product lookup tables and region lookup tables.

But many a times it happens that there are product values or codes which are not yet entered in master product lookup table but are present in the raw sales file. Is there any way that i can pass these new values to the product lookup table automatically for future references?



Eg: product code links product sale data from raw sales file with product details in the master product lookup file. In case the raw sales file throws a new code say 123 which is not yet present in my lookup table, is there a way i can automatically pass this data and related data from sales file to lookup table.
1 Reply
Highlighted

@ShijuC3 Without seeing your data model, I can't help wondering where a new product code in the raw sales data comes from, if the code has not been created in some master product list already. But, I take your word for it that you still have a need to add new products to your "own" master table.

 

In Power Query, you could easily identify product codes in the sales table that are not in the master table by merging two queries with an "anti join" (left or right, depending on which query you select fist) and remove duplicates. This will then create a table of all new products that you need to copy into your master table and complete with whatever product specific details you want to store in it. (This is similar to performing e.g. a VLOOKUP of the product code in the sales table against existing product codes in the master and then filtering all the "#NA" to find the records that have no match).

 

Once you have added the new products to the master, refresh all queries and the "new product" table should be empty and whatever lookups you had created to the master table should now include the relevant details of all new products.