Power Pivot Repeating Values

[Exceladd1ct]

Hello,

I am trying to build a simple report with data from two different tables in power pivot.

Is there a way to get the result as in the attached image since the common column "Product" has duplicates in both columns and i can't create a One to Many relation.

I use excel power pivot so Many to Many relations are not allowed.

Any sugestions to achieve this result in pover pivot, without vLookup?

Thank you.

 

[Misca]

Try this:
1) Read both tables in the Power Pivot but combine the product names with the products instead of the transactions.
2) Remove the duplicates (if possible)
3) Load both tables into Power Pivot
4) Create a many to one relationship from Transactions Products to ProductIDs Product.
5) Hide the Products and the Names from your transactions table. Might be a good idea to hide all the other columns as well and only create the measures for Products Sold and Total Sales.
6) Create the pivot table with the row labels from your ProductIDs table and the actual numbers from your measures.

 

[alansidman]

I could not do it with Power Pivot, but I was able to achieve your expected results using Power Query. Bring both tables into the PQ editor, join them on the Product. The following Mcode:

let
    Source = Table.NestedJoin(Table1, {"Product"}, Table2, {"Product"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Prod ID"}, {"Table2.Prod ID"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table2",{"Name"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"Product", "Quantity"}),
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Product"}, {{"Qty", each List.Sum([Quantity]), type nullable number}, {"ProdID", each _, type table [Product=nullable text, Quantity=nullable number, Table2.Prod ID=nullable text]}}),
    #"Expanded ProdID" = Table.ExpandTableColumn(#"Grouped Rows", "ProdID", {"Table2.Prod ID"}, {"ProdID.Table2.Prod ID"}),
    #"Removed Duplicates1" = Table.Distinct(#"Expanded ProdID", {"Product"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Duplicates1",{"Product", "ProdID.Table2.Prod ID", "Qty"})
in
    #"Reordered Columns"

There may be more efficient code