Questions: $$ For which orders have no payments been received? $($click on carrot next to Order# heading.$)$

$$ Which sales order numbers are paid off in installments $($more than one payment$)?($By noting duplicate numbers in CashReceiptsNo column$)$

$$ Which CashReceiptsNo were not applied to any orders? $($Note missing CashReceiptsNo numbers$)$

Perform a bit more data transformation, and determine:

$$ the total accounts receivable amount andbalance for each sales.

Fluffy allows their customers to pay in installments. In the current system, sales orders and payments

are recorded in two separate files. The SalesOrders file records the amount the customer owes Fluffy

and the payments that have been received. The CashReceipts file records all payments received.

$$ the balance for each sale to answer these

The main issue to be addressed is the list $($multi$-$valued$)$ format of the Payments column in the SalesOrders file. As a result, it is impossible to match the values of the Payments column in the SalesOrders table with the values in the CashReceiptNo column in the CashReceipts table. The definition of this relation is essential for the calculation of accounts receivable. This is an application of data preparation pattern $6.$

Power Query $/$ Excel is used to work this problem.

Step $1$: Open Excel. Create a new blank workbook and save it as PAC$5\_2\_$AccountsReceivable$\_$yourfirstinitiallastname. Transfer the data from the csv files to the new Excel file by importing the data from both csv files $($FluffySalesOrders$$ and $$FluffyCash Receipts$)$ provided in the Comprehensive Problem $1$ folder in DAA Learning Modules.

$$ Use the Data tab in Excel$$s main menu and select From Text$/$CSV in the Get & Transform Data group, then select the FluffySalesOrders, then click Import, then Load $($at the bottom of the screen$).$

Click on any cell in the AccountsReceivable Excel file, import the CashReceipts data by following the same steps. $($Data$>$Get Data$>$from File$>$From Text$/$CSV$,$ select file, import, then load.$)$ A new workbook in the PAC$5\_2$AccountsReceivable file will be created.

Notice the right side of the screen, Queries and Connections. Confirm that you have $10$ rows loaded for the SalesOrder data and $11$ rows for the CashReceipts data. Also confirm that you have no error messages in the box.

Step $2$: Open Power Query by going to the Data tab in Excel$$s main menu. Select the down arrow next to Get Data in the Get & Transform Data group. Select Launch Power Query Editor.

Step $3$: Restructure the content of the tables:

$$ In the Queries pane, select $($expand the width of the window$)$ and right$-$click the SalesOrder table, and select Duplicate.

$$ Rename the newly created table$$SalesOrder$(2)$as $$SalesOrder$\_$Applied$.$

$$ Select the $($original$)$ FluffySalesOrders table and select the Payments column. Press the Delete key on your keyboard.

$$ Next, select the FluffySalesOrders$\_$Applied table, and delete all columns except for the columns, $$Order#$$ and $$Payments$($by selecting the column and pressing the Delete key on your keyboard$).$

Step $4$: Convert the multi$-$valued column into a single$-$valued column:

$$ While still in PowerQuery, select the FluffySalesOrders$\_$Applied table. Select the Payments column, and then click on Home $($top ribbon$),$ and in the Transform group on the Home ribbon, click the down arrow next to Split Column, and select By Delimiter.

$$ In the Split Column by Delimiter window, select Semicolon in the field beneath Select or enter delimiter and then select Each occurrence of the delimiter underneath Split at and click OK $($Quote character should be $).$

Your screen should appear as follows:

The table portion of the screen looks as follows:

Setep $5$: You will group the data into one column. Connecting the payments in the FluffySalesOrders$\_$Applied table with the payments in the CashReceipt table is still challenging given that that they are spread across multiple columns in the former.

$$ To group them in one column, first select the Order# column in the Applied table.

$$ Go to the Transform ribbon $($NOT Home$>$transform group$),$ and in the Any Column group, click the down arrow next to Unpivot Columns and select Unpivot Other Columns.

$$ Select the $$Attribute$$ column and delete it using your delete key.

$$ Rename the $$Value$$ column as $$CashReceiptsNo$($point to $$Values$,$ right click and select rename$).$ Click off the new name.

The FluffySalesOrders$\_$Applied table should now look as follows:

Cash Receipts are now a single$-$valued column. The new table structure makes it easy to answer questions such as:

$$ For which orders have no payments been received? $($click on carrot next to Order# heading.$)$

$$ Which sales order numbers are paid off in installments $($more than one payment$)?($By noting duplicate numbers in CashReceiptsNo column$)$

$$ Which CashReceiptsNo were not applied to any orders? $($Note missing CashReceiptsNo numbers$)$

Perform a bit more data transformation, and determine:

$$ the total accounts receivable amount and

$$ the balance for each sales.