Extract value from a table and use it to remove rows in another table

[Anonymous]

Hello,

I have 2 tables.

Table 1 "Currently used Year" has a dynamic value. It only has one value, which is the year we're using in our database. This year is currently 2020, but it will change if the database is changed of course.

Table 1 looks like this:

Year

2020

Table 2 has 3 columns, including:

Category ||  Year  ||  Value 

Apple      ||  2002  ||   27401

Orange    || 2003   ||    3948

..................................................

Apple       || 2020  || 38401

Apple       || 2021  || 48090

Apple       || 2022  || 30101

Apple       || 2023  || 45000

...........................................

I want to

1) extract all the rows in table 2 with the year equal to or bigger than the year in table 1

2) each row that is has the year that is bigger or equal to the year in table 1, I want to get the value of that year / value of the year in table 1, grouped by Category

Example:

Apple  ||    2020   ||  10299  ||  10299/10299 = 1

Apple  ||    2021   ||   12000 ||  12000/10299 =  1,165

........

Thank you in advance!

[amitchandak]

@Anonymous , You can create one or measure like this and use

measure  =

var _max = maxx(allselected(Table1), Table1[Year])

return

calculate(sum(Table2[Value]), filter(Table2, Table2[Year] >=_max))

[Anonymous]

Hello,

Thank you for your answer. How can I do both 1) and 2) requests at once? And I hope to get this done in M (query editor)

[Anonymous]

Hi @Anonymous 

I think you can try to build a parameter and then filter Table2 by dynamic years you select in parameter and get result you want by M query. Here I will show you a sample.

Sample Table:

Build a Year Parameter as below steps.

Build two Custom column by M query.

Value for Select Years
=
let _Categroy = [Category]
in
     Table.SelectRows(#"Filtered Rows",each _Categroy =[Category] and [Year] = Year)[Value]{0}

Result
=
[Value]/[Value for Select Years]

Result is as below.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

Hi Rico,

Unfortunately I cannot use the parameter because the current year number will be changed, it is a dynamic number. Is there any other way I can do this?

P-

[Anonymous]

Hi @Anonymous 

Parameter List is a good way for you to change years and get different result from it in Table 2.

You can see more details in Power Query Editor in my sample.

Select 2020 in Year Parameter.

Result:

Select 2019 in Year Parameter.

Result:

Parameter list can be changed, it is dyanmic.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Rico,

Thank you for your reply. Currently I am aiming at so I do not have to do anything like changing the Parameters or adding years into the list, as in your solution.

However I have made another table (Table 3) with the Category and Value of only current year by appending the Table 1 with table 2 and did some filtering.

Table 3 is like this:

Category ||  Value  ||  Year

Orange    ||  2849   || 2020

Apple       || 8911    || 2020

Grape       || 1891    || 2020

I figure if I can input this table with table 2 based on the Category then I will get very close.

Desired table 2:

Category      ||       Year      ||       Value      ||     Value of current year (in this case 2020)

Apple            ||     2021      ||         7941      ||   3499

....

How can I do this with M? I have tried:

Table.AddColumn(each #"Table3"[Value] if [Category] = "Table3"[Category])

But it does not work. Do you have any suggestion how to fix this?

[Anonymous · Accepted solution]

Hi @Anonymous 

Try Merge Function in Power Query Editor, then expand current value in Table2.

Here I will show you a sample.

Table2:

Table3:

Merge two tables by Category column in two tables:

Expand value column in Table3.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

Hi,

Thank you. I have merged and it worked!

Cheers,

P-