Find value in column and if found get Pseudo and Sum it by Year

[automated_user]

Hi all,

Is there a way in power query to Find a value in another column, if found get the Pseudo of that row and keep it in a sum by year? For example, I have a table of records for each year and in these records is a column "PartNum". I want to find each instance a value in "PartNum" appears in the column "RowCount.PseudoLines" and if found get the "Pseudo" and keep a running total of that given year (sum). If it is not found, obviously the "SumByYear" column would have '0' as a value for that given "PartNum". See Table below:

As seen MC55 would have a total sum of 2 for the year "2022" where MC01 was not found in the year "2019", hence had 0. 

Thanks

[v-jingzhang · Accepted solution]

Hi @automated_user 

You can use the Merge Queries feature. First merge the current table to itself. Select "PartNum" and "FiscalYear" in the first table as matching columns (hold on Ctrl key to select multiple columns). Select "RowCount.PseudoLines" and "FiscalYear" in the second table. 

After merging, click the expand icon on column header of the new table column and switch to Aggregate option. Select "Sum of Pseudo" in the list and click OK to expand. 

After expanding, you will see null values on not found rows. Select that column and replace null with 0 via Transform > Replace Values. 

I have attached a demo at bottom. You can download it to see details. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.