Forum Discussion

Frequent Visitor

8 years ago

Table Nested Joint - where clause

Hello I'm builing up a summary table of sales targets and past year sales. I would like to add a column to my table containing sales of a pre-set year (ie 2015). In the following formula, I...

ricardocamargos

Continued Contributor

8 years ago

Why don't you filter the table before ?

Ricardo

Frequent Visitor

8 years ago

Ricardo

thanks for your support.

2 reasons :

- i use the year table for other queries and don t want to prefilter it

- I will use this "year sales" table to add 3 columns in the "target" table (containing the nested join fonction) : 2015 sales | 2016 sales | 2017 sales

ricardocamargos
Continued Contributor
8 years ago
patator35,

Doesn`t it work if you just create a filtered copy (you don`t need to filter the base table) ?

Ricardo
Ashish_Mathur
Super User
8 years ago
Hi,

Share a dataset and show the expected result.
- patator35
  Frequent Visitor
  8 years ago
  Table.NestedJoin(#"Valeur remplacée",{"ID_Target"},Targets_Data_Year,{"ID_Target"},"Targets_Data_Year",JoinKind.LeftOuter)
  = Table.ExpandTableColumn(#"Requêtes fusionnées3", "Targets_Data_Year", {"Year", "Qty"}, {"Targets_Data_Year.Year", "Targets_Data_Year.Qty"})
  
  Here is the final table that will be loaded in Excel (and will be used as source for a pivot table) :
  
  Here is the table containing sales per year ("Targets_Data_Year"):
  
  Here is the table containing sales targets ("Targets") - I want to add column 2014,2015,2016... (containing qty/sales of the above table)
  - Anonymous
    Not applicable
    6 years ago
    Hello,
    have you managed to find a solution?
    Something like this would benefit me so much.