Excel Power Query Table Creation
Budget: $10 – $30 USD
Please help me in making the power query table in excel based on the following information.
Attached excel file have three tabs named PMS Data, TRXCDE and CAT
PMS Data is generated directly from the Opera Cloud and need to use for any given period, and the TRXCDE sheet is manually updated to identify the revenue category based on the transaction codes in the PMS Data sheet and CAT is he available revenue categories as of now.
In the PMS data sheet, the transaction codes are in column AC, transaction description is in column Q, and Cashier debit amounts are in column X.
What I need is that to summarize the cashier debit amounts by the category in the TRXCDE sheet as in the attached screenshot. Also all the categories in CAT should be appear in the report regardless of revenue are there or not in the PMS Data sheet. If there are are new category added to that sheet, the respective category should be appeared in the report. However the category #N/A ahould not be appear in the report neither in the grand total column. I believe that we have to merger three sheets in the power query?
Key points to note:
• categories with #N/A should not be in the table should not be calculated in the grand total column.
• All the other categories in the CAT sheet should be included in the table regardless of transaction is there or not in the PMS Data. Only the category #N/A should be excluded.
• Only the Power Query Pivot should be made. Not direct Pivots.
Please give me the step-by-step guidance on how to do it as I am a very beginner to the power queries.
I have done it but it doesn’t get refreshed when the PMS data is for longer period. It get refresh few day like 1 to 5 days, also I was not able to fixt the categories in CAT sheet. I can show it to experts who attend to this task.
Attached excel file have three tabs named PMS Data, TRXCDE and CAT
PMS Data is generated directly from the Opera Cloud and need to use for any given period, and the TRXCDE sheet is manually updated to identify the revenue category based on the transaction codes in the PMS Data sheet and CAT is he available revenue categories as of now.
In the PMS data sheet, the transaction codes are in column AC, transaction description is in column Q, and Cashier debit amounts are in column X.
What I need is that to summarize the cashier debit amounts by the category in the TRXCDE sheet as in the attached screenshot. Also all the categories in CAT should be appear in the report regardless of revenue are there or not in the PMS Data sheet. If there are are new category added to that sheet, the respective category should be appeared in the report. However the category #N/A ahould not be appear in the report neither in the grand total column. I believe that we have to merger three sheets in the power query?
Key points to note:
• categories with #N/A should not be in the table should not be calculated in the grand total column.
• All the other categories in the CAT sheet should be included in the table regardless of transaction is there or not in the PMS Data. Only the category #N/A should be excluded.
• Only the Power Query Pivot should be made. Not direct Pivots.
Please give me the step-by-step guidance on how to do it as I am a very beginner to the power queries.
I have done it but it doesn’t get refreshed when the PMS data is for longer period. It get refresh few day like 1 to 5 days, also I was not able to fixt the categories in CAT sheet. I can show it to experts who attend to this task.
Related categories:
Visual Basic
Data Processing
Excel
Software Architecture
Excel VBA
Data Visualization
Data Analysis
Data Management