Hello everyone,
I'm procesing some financial data and have come across an issue I need help with.
Since financial information is usually disclosed as is, when importing the information everything comes along. However, I wish to calculate the subtotals in a pivot table.
What I normally do is create a supporting table with an "Account" column and a "Sub-Account" column. Then I merge both tables using Inner Join between the source file's "Account" column and the supporting table's "Sub-Account". That way only the sub-account concepts remain in the merged table, along with a new column with the "Accounts" column from the supporting table that will help me create the subtotals in a pivot table.
This time around, however, I'm having issues since the source file's "Accounts" column has an expresion that repeats for more than one "Account" or subtotal concept. So when I try the abovementioned way, I end up with bananas in the pivot table instead of the actual subtotals.
The expresion in question is "Ajuste por reexpresión" (sorry for the Spanish), and it appears five times under 5 different subtotals. I've tried all Join Kinds that power query offers to no avail. Each gave me a different result, but not the correct result.
At present, it's a 60 file query, and they'll keep coming, so I'm hoping to find an automate solution rather than changing the concepts by hand in each file each time...
I'm attaching a file where the expresions from the source file's column and their respective subtotals can be seen, and the supporting table I originally created.
File here: https://1drv.ms/x/s!AmrjlXSYqMxegZpBTwadY7uHeI50Aw?e=yLnNSl
Thanks in advance,
Shinka