# Create a default prodItem by CustID

• ### Question

• Hi,

I have a file with Custid and product attribute 'A', 'B'. If Custid has both 'A' and 'B' then Proditem 1001, if 'A' then 1002, If 'B' then 1003 else (neither 'A' or 'B') then 1004. Is this possible to solve in PQ?

Lars Wärvik

Thursday, May 7, 2015 2:08 PM

• It's hard to provide a specific answer without understanding what the input data looks like. Here's an example I invented based on one possible interpretation of your question. It's not achievable via the UI. Perhaps if your input data looks different, it can be accomplished entirely inside the UI.

let
Source = Table.FromRows({
{ 1, "A" },
{ 1, "B" },
{ 1, "C" },
{ 2, "A" },
{ 2, "C" },
{ 3, "C" },
{ 3, "B" },
{ 4, "C" }},
type table [Custid=number, #"Product Attribute"=text]),
Grouped = Table.Group(Source, {"Custid"}, {{"Attributes", each [Product Attribute], type table}}),
Transformed = Table.TransformColumns(Grouped, {"Attributes", each
if List.Contains(_, "A") and List.Contains(_, "B") then 1001
else if List.Contains(_, "A") then 1002
else if List.Contains(_, "B") then 1003
else 1004 })

in
Transformed

Thursday, May 7, 2015 3:53 PM

### All replies

• It's hard to provide a specific answer without understanding what the input data looks like. Here's an example I invented based on one possible interpretation of your question. It's not achievable via the UI. Perhaps if your input data looks different, it can be accomplished entirely inside the UI.

let
Source = Table.FromRows({
{ 1, "A" },
{ 1, "B" },
{ 1, "C" },
{ 2, "A" },
{ 2, "C" },
{ 3, "C" },
{ 3, "B" },
{ 4, "C" }},
type table [Custid=number, #"Product Attribute"=text]),
Grouped = Table.Group(Source, {"Custid"}, {{"Attributes", each [Product Attribute], type table}}),
Transformed = Table.TransformColumns(Grouped, {"Attributes", each
if List.Contains(_, "A") and List.Contains(_, "B") then 1001
else if List.Contains(_, "A") then 1002
else if List.Contains(_, "B") then 1003
else 1004 })

in
Transformed

Thursday, May 7, 2015 3:53 PM
• Thanks Curt,

I will try to use your example and you know the progress.

Lars Wärvik

Thursday, May 7, 2015 4:47 PM
• Hi Lars,

Have you had a chance to give Curt's suggestion a try yet?

Regards,