none
Create a default prodItem by CustID RRS feed

  • 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

Answers

  • 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,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Wednesday, May 13, 2015 8:51 PM
    Moderator