As discussed in the previous video, we're going to kick off this sales report by cleaning the data set from duplicates nulls incorrect rounding, and then perform some basic calculations. Let's start by importing our supermarket data spreadsheet. So we'll go to the Insert tab, drag in the input data tool, and connect to 10.0 supermarket data. Since we've already walked through the data set in the previous video, we'll dive straight into removing these duplicates. Let's drag in the unique tool from the preparation tab and connect it to our input data tool. You will notice that there's a U and D output anchor which means unique records will go to you and duplicate records will go to D. In the configuration pane.

Let's select all items. The reason why we're selecting all fields is because it's perfectly possible To sell an item of similar description, but over two sections or aisles. For example, we have cottage cheese in dairy, and another cottage cheese in the cheese section. If we select all fields, that means both cottage cheeses stay since they're in different sections, but if we select the item only, it will remove the second version of cottage cheese. However, since we want to keep both, we're going to select all unique fields. Let's add a browse tool to both unique duplicates for easy viewing.

So let's press Ctrl Shift B, and then we'll run our workflow and check the outputs. We should have 90 unique records and nine duplicate records. Now it's time to update the section column. What we're going to do is similar to an Excel v lock up, or look up the item in our supermarket data into the supermarket items table spreadsheet. So let's drag in another input data tool and connect this to our supermarket items table spreadsheet. As you can see, this contains a clean list of all the items in each section.

We'll now go to the join tab and drag in a find replace tool. And we'll connect the unique output anchor from unique into the find input anchor from our find replace tool will then drag the input data anchor from supermarket items table into the Replace anchor. In the configuration pane on the left, we want to match the entire field and find the item within our supermarket data And compare it to the item name in the supermarket items table data. Once it's found a match, we then want to append values to the end of the data set. And we want to append it to the section column. Let's add a browse tool with Ctrl Shift B.

Let's run our workflow with Ctrl R. And now we have a section with no more nulls. But it's been appended to the end, and it's called section two, because we already have a column called section. We want the section next to the items. So let's fix this by going to the preparation tab and dragging in the Select tool. In the configuration, we'll move section two all the way up. So it's under Section We'll untick the original section, and rename section two to section.

While we're here, let's update the profitable column to type into 16. Because if an item is profitable, I wanted to return a one since this needs to be an integer. Let's add a browse tool with Ctrl Shift B and run our workflow with Ctrl R. And you should now have section completely populated. The next thing we need to update is the unit sold column. We're currently selling fractions of items when we should be selling whole numbers. So staying in the preparation tab, let's drag in a formula tool and connect it to the output of our find replace tool.

Let's put a formula in for unit sold. And we'll select the function of round. If it doesn't appear in your list here, you can type in round and click on that. The first parameter x is asking us which column we want to round. So we'll click on the associated variables button here and select units sold. The last parameter is asking us to which multiplier we want to round up to.

We want a multiplier of one which will make it a whole number. Let's add a browse tool with Ctrl Shift B and run our workflow with Ctrl R. And now we have our unit sold nicely rounded up. So sticking with the formula tool, we're going to calculate our total revenue, total cost of goods sold profit margin and that profitable flag. Let's add a new formula for Total Revenue, which is going to be the sales price multiplied by unit sold will add a formula for total cost of goods sold. And that's going to be our purchase price plus other Cost of Goods Sold multiplied by the unit sold. And then for profit margin, this will be calculated as profit divided by total cost of goods sold and profit is calculated with total revenue minus total cost of goods sold divided by total cost of goods sold.

And then lastly for profitable which we will Want to be a one or zero, we'll use the IF function. And the condition will be if profit margin is more than zero, then output a one. Otherwise output No. Let's run our workflow with Ctrl R. And now we have our total revenue, total cost of goods sold, profit margin and our profitable flag calculated. Let's take a break there because that was a lot of information to absorb. In this section of our workshop, we've cleaned up our data by removing duplicates, filling in nulls, rounding up down numbers, calculating totals, and even figured out if an item is profitable.

Now that our data is clean and ready to use in the next section of this workshop, we'll go over creating Our supermarket report with charts