Power querry: condition from another querry

[telesien]

Hi, I am pretty new to the whole power querry thing and I have a question.

I would like to simplify monthly cost report, but I need to remove some data, because they would create duplicate inputs.

The simple thing would be to simply create conditional column where the value in column [Costs] would be set to 0 for all specific condition in column [Cost element]

The problem is, that the types of cost elements I need to filter out this way is pretty long. I have them saved in another querry that is connected to this main input one by relationship and all of them have common name.

The question is: can I use parameter from other querry for conditional column? I understand that I would probably have to use custom column and type it myself, but although I am very new to M, I have some experience with VBA and it shouldn't be that different, so I should manage.

Thank you

 

[pjmorris]

not sure I fully understand the question, but as I read it I was thinking whether a table merge might be at least part of the answer. You can create a variety of 'joins' and I suspect there is at least one combination that would help eliminate the unwanted data.

Hope this helps a bit!

 

[telesien]

OK, I will try to be more clear.

There are two tables.
Table one has data from SAP. Many columns with useful information I want to create pivot table from, so people can easily get the details just by clicking into the table. This table first need to be modified so the cost values don't include duplicate data. This can be done using column [Cost element]. It stores many ID codes (about 200 unique values) repeated many times (we are talking about thousands of rows)

Cost element	Costs
1111	2000
1211	1500
1311	2200

While the other table gives those cost elements names like this

Cost element	Name
1111	Duplicate
1211	Duplicate
1311	Material

And I need to set value to 0 in the first querry for all the cost elements ID that translate to Duplicate in the second querry. They are too numerous and new can be added, so simply listing them in conditional column is not really an option.

I also can't lose the ability to get the details from many columns in the pivot table and the ability to quickly update by simply adding new file each month to the querry folder.