How to refresh data and update from one table to another

Hi Pitsere 
Thanks for the sharing the pbix to my personal chat. I have opened your "Master Product" query and here's exactly what's happening:

Source = Table.Combine({ "Revlon Product Master Abb", "EA Product Master (ArTrnDetail)" })
       -> Removed Duplicates

Master Product is an append (Table.Combine) of two product-master tables, and that's the last step. There is no merge to InvMaster anywhere in it, so nothing from InvMaster (Category, Brand, etc.) can ever land on it and no matter how many times you refresh.
The relationship `Master Product[Product code] -> InvMaster[StockCode]` only  filters at query time; it never copies columns back. That's the whole reason it looks  static.

You have already use the correct pattern in your own InvMaster query, it does a `Table.NestedJoin` + expand to pull Brand/Category/Sub Brand from "InvMaster+ inventory master (EA)".
Master Product just needs the same treatment.

Workaround is to add a Merge as the last steps of Master Product : 

1. Power Query -> select  Master Product.
2. Home -> Merge Queries(into the existing query).
3. Top table:  Master Product , click  -> Product code. Bottom table:  InvMaster , click  StockCode .
   Join kind:  Left Outer.
4.  Click the expand arrow on the new  InvMaster column, uncheck "use original column name as prefix", and tick the fields you want (e.g.  Category, Brand, Sub Brand).
5.  Close & Apply. 

Or just paste this over your current Master Product query (Advanced Editor):

let
    Source = Table.Combine({#"Revlon Product Master Abb", #"EA Product Master (ArTrnDetail)"}),
    #"Removed Duplicates" = Table.Distinct(Source),
    // normalise the key so matches don't come back blank
    #"Clean Key" = Table.TransformColumns(#"Removed Duplicates", {{"Product code", each Text.Trim(Text.From(_)), type text}}),
    // MERGE (not append) to pull columns from InvMaster by product
    #"Merged InvMaster" = Table.NestedJoin(#"Clean Key", {"Product code"}, InvMaster, {"StockCode"}, "InvMaster", JoinKind.LeftOuter),
    #"Expanded InvMaster" = Table.ExpandTableColumn(#"Merged InvMaster", "InvMaster", {"Category","Brand","Sub Brand"}, {"InvMaster Category","InvMaster Brand","InvMaster Sub Brand"})
in
    #"Expanded InvMaster"

 Now every refresh re-runs the merge and Master Product stays in sync with InvMaster.

Two things to keep and eye:

• If the new columns come back blank, the keys don't match exactly. Set both `Product code` and `StockCode` to  Text and apply  Transform -> Format -> Trim before merging (the M above already trims Product code).
• Franchise: heads-up that InvMaster has no Franchise column only Category/Brand/Sub Brand. So Franchise can't come from InvMaster. If Franchise also needs to refresh, merge it (same technique) from the table that actually holds it per product your Revlon Product Master Bridge has Franchise code/description keyed by Product code.

Thanks,

Srikanth Cheri

Community Support Team

Hi Pitsere 

Yes, replacing the data inside the PBIX works fine for static mappings. Two things:

1. Match column names & order exactly (I checked your PBIX):
   EA Brand Categories (Manual input) →  Product Class | Brand | Franchise code | Franchise description | Business ID | Brand Group 
   EA Categories  →  Category Code | Category | Main Category | Category Group Code | Category Group 
   Your attached Excel has a different order and looks like "Franchise code" appears twice (one is probably  Category Code ). Fix the headers first, or the merges/relationships will break.
2. How to replace:
   Transform data → select the table → Home → Enter Data → paste rows from Excel → name it the same as the original → replace old query.
   Repeat for the second table → Close & Apply.
3. Still apply Part B (the cleaned `EA Brands Manual Input` query without `Table.Skip`). Without it, the downstream table still corrupts when Syspro row counts change.

Thanks,
Srikanth Cheri
Community Support Team