Identify product based on another table

[Oros]

Hello,

I have 2 tables.  How do you identify which products are NOT counted?  How do you relate these 2 tables.   Thanks.

[Ashish_Mathur]

Hi,

Ensure that there is a Many to One relationship from the Count table to the Product table.  To your visual, drag Product from the Product table.  Write this measure

Count = coalesce(countrows(Count),0)

You may now filter the measure on 0.

Hope this helps.

[Oros]

Hi @Ashish_Mathur,

Thank yo for your reply.  I am getting a cardinality error for many to one relationship.  Any ideas?  Thanks again.

[Ashish_Mathur]

Ensure that there are no duplicates/blanks in the Products table.

[adudani]

hi @Oros ,

in power query, you could possibly do a full outer join. Filter the key columns on either table = blank and do a count.

create 3 blank queries and copy paste the code below into the advanced editor:

INPUTS: 

count 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgwI8HFV0lHySsxTitWJVnJy9ANCoIBLajJYwD/I0c8dpMI/uQQs4B7qGAZS4JdfphQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, #"Date Counted" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Date Counted", type text}}),
    #"Uppercased Text" = Table.TransformColumns(#"Changed Type",{{"Product", Text.Upper, type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Uppercased Text",{{"Product", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Product", Text.Clean, type text}})
in
    #"Cleaned Text"

 product

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgwI8HFVitWJVnJy9ANCMNM/yNHPHSLqC2T5g1lBjsEBTq5BQZGhYG5wSJBjOJivFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}}),
    #"Uppercased Text" = Table.TransformColumns(#"Changed Type",{{"Product", Text.Upper, type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Uppercased Text",{{"Product", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Product", Text.Clean, type text}})
in
    #"Cleaned Text"

output  (get the products in product table but not in the count table) : 

let
    Source = Table.NestedJoin(Table, {"Product"}, Count, {"Product"}, "Count", JoinKind.LeftOuter),
    #"Expanded Count" = Table.ExpandTableColumn(Source, "Count", {"Product", "Date Counted"}, {"Product.1", "Date Counted"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Count", each ([Product.1] = null)),
    #"Counted Rows" = Table.RowCount(#"Filtered Rows")
in
    #"Counted Rows"

[Oros]

Hi @adudani ,

Thank you for your quick reply.  Should I replace the 'Product' with the actual product name table?

[adudani]

yes. 

or you could copy the steps after source if you'd like.