Power BI Fitness and Meal Planning with Excel

This post demonstrates a Power BI Fitness and Meal planning solution based on Excel data. Power BI is an excellent tool for visualizing important data about the food we consume and provides a method to quickly and easily build an a powerful interface to help with meal planning education and ideas. The purpose here is to demonstrate how you can build and customize your own visual aids to use alongside the excellent resources provided by the USDA (Choose MyPlate), US Department of Health and Human Services (Body Weight Planner), and other government organizations.

As a reference, I used the “MyPyramid Food Raw Data” download spreadsheet available at https://catalog.data.gov/dataset/mypyramid-food-raw-data-f9ed6 to build this demo application. You can find similar trustworthy information across the many sites  sponsored by health-centered US government agencies. Specifically, I used the “Food_Display_Table” Microsoft Excel workbook that is included with the download:

Import the Excel Workbook into Power BI

Launch Power BI Desktop, and choose the “Excel” option from Home > Get Data:

Next, choose the Food_Display_Table workbook, and when the data-load completes, choose “Table1” from the data navigator. Click the “Load” button after the preview sets-up as no changes need to be made in the Power Query Editor for this report:

Take a look at the data and familiarize yourself with some of the key columns using the Data view in Power BI Desktop. For this tool/report, you’ll need to understand and be ready to use: Display_Name, Portion_Default, Portion_Amount, Portion_Display_Name, Calories, and Saturated_Fats.

Power BI Fitness and Nutrition Columns and Measures

Don’t get scared off by the term “Measures”. For our scenario, it simply refers to a new value we’ll create that holds the total calories and total saturated fats in a serving of food. We need to do this calculation because the “Calories” values in this spreadsheet are for a set portion, and some servings consist of more than, or a partial amount, of one portion….we’ll get back to that after we rename a few columns.

Rename Columns

Flip back to the Report view in Power BI Desktop, and rename columns by clicking the ellipsis and choosing Rename.

Rename columns as follows:

• Display_Name to Food
• Portion_Amount to Portion Size
• Portion_Default to Portion Multiplier
• Portion_Display_Name to Portion
• Saturated_Fats to Saturated Fats

Create Measures

In the Home tab on the ribbon, click “New Measure”. Then in the editor window that appears, enter the following:

Check the check-mark in the upper left hand corner of the measure formula editor to save. Add another measure, and specify it as follows:

If this doesn’t make complete sense, that’s fine. This reads, for example, “Create a new measure named ‘Serving Saturated Fats’ which is the ‘Portion Multiplier’ * ‘Portion Size’ * ‘Saturated Fats'”. This way saturated fats will be calculated for the standard serving, not a single portion. The SUM() function is related to the way in which Power BI handles numerical data, and though it’s kind of irrelevant for our upcoming report, it is the behavior that we want. You should see your two new measures in the Fields pane:

Power BI Fitness and Meal Planner Interface

Give the video below a watch for a walk-through on how to build the report interface. Beforehand, let me explain what I’m going to do in sequence. Here’s what you need to take away as you watch the video:

• Add a Multi-Row Card visualization. This will show all of the information about selected foods and portions.
• Add a Slicer visualization. This will let you slice the meal plan by specific foods.
• Add another Slicer visualization. This will let you slice the the meal plan by specific portions for the selected foods in the meal plan.
• Add a Clustered Column Chart visualization. This will help you see, graphically, which foods in the meal plan account for the most-to-least calories.
• Note that, when I add the Slicers, I turn “Multi-select with CTRL” to the value “Off“. This is so Multi-select is enabled by default. I stumble here the first time, so please pardon the miss-clicks…it’s cleaner when I add the second slicer.

Ready?

At this point, you have a fully functional meal planner. You should feel free to go through other settings on the visualizations and update the controls to have a look and feel that helps you to use the tool more effectively. Maybe the bar chart doesn’t work for you, and you’d prefer a pie chart to show calories per food item against a grouped total. You may even have an Office 365 tenant where you can publish this to the web. Another great addition would be to add a new measure which sums up the total calories for all of the foods in your selection.

Here’s where I left off for the demo – a fully published web version of the Power BI report on Microsoft 365.

Thanks for reading.

Categories: Microsoft Excel, Office 365 and O365, Power BI

Tags: Office, Office 365 and O365, Power BI