Forum Discussion

Dan Cloutier's avatar
Dan Cloutier
Copper Contributor
Feb 09, 2018

Pivot Table or some other way to solve this issue?

Problem: I want to create a list of all accounts that have ordered 1-3 pallets in the last 12 months of Product A  but have also ordered 20 or more pallets of Product B in the last twelve months.   What is the best way to solve this issue?

BI & Data Analytics
Need Help

10 Replies

  • Mehdi HAMMADI's avatar
    Mehdi HAMMADI
    Brass Contributor
    Feb 12, 2018

    I created a dataset with 4 fields [Accounts], [Dates], [Product A] and [Product B]
    and I applied the transformation below using "Get & Transform" (Power Query).

    Not sure it is the best way but try it and let me know :

    let
   Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
   #"Changed Type" = Table.TransformColumnTypes(Source,{{"Accounts", type text}, {"Dates", type datetime}, {"Product A", Int64.Type}, {"Product B", Int64.Type}}),
   #"Apply Conditions" = Table.AddColumn(#"Changed Type", "Keep", each if ([Product A] >=1 and [Product A] <=3 and [Product B]>=20 and [Dates] >= Date.AddYears(DateTime.LocalNow(),-1) ) then 1 else 0),
   #"Keep Good Records" = Table.SelectRows(#"Apply Conditions", each ([Keep] = 1)),
   #"Removed Duplicates" = Table.Distinct(#"Keep Good Records", {"Accounts"})
in
   #"Removed Duplicates"


    Hope this will help
    Regards
    Mehdi

    • JKPieterse's avatar
      JKPieterse
      Silver Contributor
      Feb 13, 2018
      I'm sorry if I didn't make myself clear, I was wondering if you could share some example records of the data you are trying to summarize, not of the query itself. As I do not have access to your data the query does not help much :-)
      • Mehdi HAMMADI's avatar
        Mehdi HAMMADI
        Brass Contributor
        Feb 13, 2018

        Hi Jan,

        I fully understand your answer to Dan and it seems to me that it is up to him to provide a sample of data to solve his problem.
        Not providing data, I imagined a dataset that could do the trick.
        In the example in attachment, you will find the starting data and the result and I have commented the query.

        Let me know if that was helpfull

         

Resources