Forum Discussion
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. W...
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
- 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 :-)
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
- Hi
Sorry but the sollution I proposed is out of contexte.
The result is a list accounts that had at least placed an order with 1-3 pallets of product A and 20 pallets or more of product B during the last 12 months.
