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...

Frequent Visitor

8 years ago

Hello again

I'm really stuck and would appreicate any kind of support helping me to advance.

Maybe my question was not clear, please let me know if this is the case...

thanks

ricardocamargos

Continued Contributor

8 years ago

Why don't you filter the table before ?

Ricardo

patator35
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)