I am currently working through some fresh logic to make it easier for users to map a Chart Of Accounts to report lines. For my SQL server customers this all works fine and quickly, but I’m struggling to get the logic to run quickly in XL/PBI PQ for my XL sourced customers.
The user will map a single account from the Chart of Accounts to a single report line ONCE. Then on the report page he will, in any of Columns SMap01 to SMap10, determine where the Report Line sums to. The mappings do not necessarily follow a hierarchical structure allowing the user the facility to easily perform any sub analysis requirements, aside from the usual financial statements.
Two attempts at this in the attachment – I will describe the process in PQ Folder LedgerAlloc4.The task is to unpivot SMap01 to SMap10 into a single MappingKey column. In the example this is query “Unpivoted SMap”. Then reference this query as “Collect All mappings” and perform multiple merges to build the mapping table. So merge Mapping Key to RepKey and return MappingKey, rename as Map01, merge Map01 to RepKey and return MappingKey, rename Map02 etc.
With the example file this involves 20 merges.
Once this is complete we need to unpivot the 20 columns and merge with the Chart of accounts to provide a final map of each RepKey to ledger code. Each reporting line will have multiple rows for each Mapping Key/RepKey combination. A final merge will map the ledger code to the RepKey. For example, all PBT related ledger codes will map to PBT.
In the example the column ExpenseCode should be understood as a LedgerCode – quirky terminology from the ERP system used for example purposes.
An earlier version of this is contained in PQ folder LedgerAlloc, using nested joins which was very slow. I saw Chris webs article and worked it through with Table.Join and Table.Buffer and reworked in LedgerAlloc4 (Still refreshing whilst I’m typing this!) I have also tried a non-merge approach using List.PositionOf but that didn’t really work either.
So having tried all this I’m big time stymied as to where to go next, especially since I can’t get the 20 merges I need in some cases. The desired output is shown on a dedicated tab, SQL Server script which works also attached. In the demo file I can no longer get either version of the query to refresh.
Looking forward to help
Pete
MAStructureEDNA2.xlsx (962.9 KB)
LedgerAllocation.sql (3.5 KB)