Adaptive Planning

Workday Adaptive Planning Tips & Tricks: Data Organization with Microsoft Power Query

November 1, 2023 by Brian Colucci

In the world of business, data analysis and interpretation are essential to creating effective, well-informed decisions. However, messy and unorganized data can create serious roadblocks in this process, leading to inefficiencies, and lost opportunities. Fortunately, Microsoft Power Query is a powerful tool designed to eliminate the headaches and tedious tasks related to creating clean and accurate data. The first step is to organize your data.

What is Microsoft Power Query?

Microsoft Power Query is in Excel under the data tab under the “Get and Transform Data” group option. It is designed for data transformation that can also be used for other Microsoft applications such as Power BI and Power Automate. The tool allows users to connect to various data sources, transform data into the desired format, and load it into their preferred destination in ways that native Excel functions such as formulas and pivot tables cannot.

Benefits of Using Microsoft Power Query

Data from Multiple Sources: Power Query acts as a one-stop shop for you to connect to a wide range of data sources, including databases, spreadsheets, web services, and more. This means you can gather data from multiple sources in one place for analysis.

Data Transformation: With Power Query’s intuitive interface, you can easily clean, reshape, and transform data without writing complex code. It offers a range of functions and transformations to suit your needs.

Automation: Power Query enables automation of data extraction and transformation processes. This reduces manual errors and saves time, especially when working with large datasets.

Reusable Queries and Data Volume: The tool saves a history of previous queries for future use. This allows users to reuse processes for similar datasets, saving time and effort. Additionally, queries can import copious records of data that would not fit into a standard Excel worksheet.

Data Connection: Updates from the source data are seamlessly integrated into your Power Query process. This ensures that your analysis is always based on the most up-to-date information.

Data Organization with Power Query

1. Data Connection

To start organizing and cleaning data using Power Query, you need to establish a connection to your data source. In this example, the data is in an Excel worksheet as a table.

Loading the table can be done via the “From Table/Range” option in Excel or the relevant data source connector in other Microsoft applications.

2. Data Import and Preview

Once your data source is connected, Power Query displays a preview of your data. This step allows you to review the data’s structure and make initial decisions about what needs to be cleaned or transformed.

3. Removing Duplicates

Duplicate data can skew your analysis. Power Query provides a simple way to remove duplicate rows, ensuring that your data is clean and accurate. To do this, highlight the desired columns and select “Remove Rows” and the “Remove Duplicates” option.

4. Filtering Data

You can filter data based on specific criteria. This can be helpful when you need to focus on a particular subset of your data. Find the drop-down menu above each column header to easily filter your data.

5. Data Transformation

Power Query provides a wide range of transformation options. You can perform tasks like splitting columns, merging data, renaming columns, and more. These transformations help you reshape the data to fit your analysis needs. To rename, simply double-click the column header and type the desired name.

6. Reporting

After making the desired changes to your data, return the data back to excel by selecting the “Close & Load” option and selecting “Close & Load To.” Then select the desired output in the following window.

Additionally, Pivot tables can be used to retrieve data directly from Power Query. This can be helpful when the data set does not fit in a native Excel spreadsheet. To do this, select “Close & Load” in Power Query, and select “Close & Load To.” From the following dialogue box, select “Only Create Connection.” Now you can insert a Pivot Table using an external data source and select your query.

Microsoft Power Query is an indispensable tool for professionals working with data. Whether you’re dealing with financial records, customer information, or any other dataset, Power Query simplifies the process of data organization and cleansing, saving you time and ensuring data accuracy.

Check back soon for our next blog post on Microsoft Power Query. We’ll share tips on using Microsoft Power Query to cleanse your data.

Revelwood is an award-winning, Platinum Workday Adaptive Planning partner. We build solutions for the Office of Finance that minimize your risk by seamlessly incorporating business analytics into your everyday thinking. Combining the software with our best practices and out-of-the-box applications, we help businesses achieve their full potential with Workday Adaptive Planning.

Workday Adaptive Planning Tips & Tricks: Restrictions on Cube Sheets

October 18, 2023 by Ben Alcock

When using Workday Adaptive Planning, cube sheets can often be bogged down by a large number of elements, such as dimensions and accounts, that aren’t needed for the model in question. Workday Adaptive Planning lets us limit and filter the version, accounts, levels and dimensions using the native restrictions function.

Using the restrictions function, we can limit a sheet to bring through different accounts on the actuals version than in the forecast, specify certain accounts that only have data in certain dimension values, filter accounts to appear at different levels and much more.

A cube sheet restriction has two elements: the first element determines the restriction and the other element is what will be restricted. The way to add a restriction to a cube sheet is to open up the back end of the sheet through modeling and in the sheet’s summary, click restrictions, click add new, and pictured below, you can see what options we have to restrict certain elements:

After selecting the elements for the restriction on this screen, click create and continue. On the next screen, you can see the two elements you chose ready to be restricted. For example, if you create an Account/Asset-Dimension restriction, you can specify which asset classes are allowed to be shown for any given set of accounts. Here you can see I’ve restricted depreciation accounts on a capital expense cube to just the Software/License asset class:

By clicking save in the top left corner, you have successfully created a restriction on your cube sheet. You can use the same restriction multiple times, as long as the two elements are the same. In our example, I could unselect the total depreciation account and choose fixed assets to filter on asset classes.

Revelwood is an award-winning, Platinum Workday Adaptive Planning partner. We build solutions for the Office of Finance that minimize your risk by seamlessly incorporating business analytics into your everyday thinking. We help businesses achieve their full potential with Workday Adaptive Planning by combining the software with our best practices and expert guidance.

Workday Adaptive Planning Tips & Tricks: Metadata Loaders

October 4, 2023 by Julia Seelin

A key component of Workday Adaptive Planning is the ability to pull in actual data through integration. However, there is more you can do with this access to the data source. There is the ability to load in dimensions from the data source and have them automatically added to that specific dimension. This is done through a Metadata Loader. A Metadata Loader can only bring in one dimension at a time, but it will connect to an existing dimension and add any new values that are loaded into the data source.

In order to create this loader, the correct data table must be brought in the source data, as depicted below.

The next step consists of creating the planning dimension loader and filling out the Data Source Settings for the dimension. You would select the newly added data table as the source table. You then select the planning dimension it correlates with already in the instance.

Lastly, you map the columns, based on the source table, to ensure the necessary details are pulled in.

In order to ensure the correct names and codes are being pulled in correctly, it is recommended to run a preview of the output. If the loader was done correctly, a file will be produced that displays the desired information. From here, the loader is ready to be added to a task so that it will run as often as desired.

Workday Adaptive Planning Tips & Tricks: How to Create Dimension Attributes

February 22, 2023 by Ben Alcock

Dimension attributes in Workday Adaptive Planning are very helpful when we want to further separate data from the same dimension or give a hierarchy to a flat dimension. In this example, I use the dimension “New Jersey Apples.” We can tag each dimension value with an attribute that lets us know if it’s either a red apple or a green apple.

Select the three lines from next to the Workday W and navigate to modeling and dimension attributes. Click the Create new dimension attribute button

at the top of the sheet and name your attribute. In this example, our attribute name is apple colour.

Next, we need to populate our attribute values. Like when creating dimensions [add link to previous blog post], there are three methods for doing this.

The first is to do it manually. Select the create new attribute value button

from the top of the sheet. Next, create each attribute with a name and unique code. For example, red and green.

The second method is via an import. To do this, we need an import template. Click the Import dimension attribute structure button

and download an import template. Open it, and in column A on the first row only, write the name of your overall dimension. In the next row, in columns E and F, include the name and unique dimension value code from your data source. Save and return to Adaptive Planning. Choose your new file and click import. If all goes well you’ve saved a ton of time populating your new dimension attributes!

The third approach is by far the easiest and fastest method. Select the check box Data import automatically creates new dimension attribute values:

The parent dimension attribute is already created. Tagging the dimension values you’re going to import with a nonexistent dimension attribute value will cause Adaptive Planning to automatically populate your new dimension attribute.

And that’s it for creating dimensions attributes!

Visit Revelwood’s Knowledge Center for our Workday Adaptive Planning Tips & Tricks or sign up here to get our Workday Adaptive Planning Tips & Tricks delivered directly to your inbox. Not sure where to start with Workday Adaptive Planning? Our team here at Revelwood can help! Contact us info@revelwood.com for more information.

Apex Entertainment Performs Enterprise Planning with Workday Adaptive Planning

February 9, 2023 by Revelwood

How do you move away from spreadsheet-based budgeting, forecasting and reporting for more insight into the operations of the business? With Workday Adaptive Planning.

Apex Entertainment, headquartered in Marlborough, MA, operates four family entertainment centers in the northeast U.S., including Virginia Beach, Syracuse, Albany and Marlborough. Apex offers attractions that are fun for all ages, with activities such as Indoor Go Karts, Bowling, Laser Tag, Escape Rooms, Ropes Courses, Arcade and Redemption, Sports Simulators, Axe Throwing, Mini Gold, Bumper Cars, Virtual Reality and state-of-the-art event meeting space. Each location offers a full-service dining experience in The Pit Stop Tavern, with a menu that includes 80 gluten-free options as well as vegetarian options.

“Relying on Microsoft Excel to manage our financial processes handicapped us more than helped us,” said Marcus Kemblowski, COO, Apex Entertainment. “We needed a solution that gave us insight into how to fix problems and would enable us to manage our business in real-time.”

Apex Entertainment selected Workday Adaptive Planning and Revelwood. The goal of the implementation was to easily see how the business is doing, down to the location and the attraction. Now, with Workday Adaptive Planning, Apex Entertainment has the information available to make strategic decisions. The team can analyze and assess potential expenses regarding forecasted revenue by activity.

“From the get-go, Revelwood clearly understood our business. They immediately knew what we were discussing and hot to get us to where we needed to go,” added Kemblowski.

Apex Entertainment is not done with its plans for Workday Adaptive Planning. “Workday Adaptive Planning can help us to get where we want to go,” commented Kemblowski. “We now can easily perform top-line to bottom-line budgeting and forecasting. The insights generated by the application are invaluable to the company’s growth.

Interested in learning the full story? Read the success story to learn how Apex Entertainment benefits from Workday Adaptive Planning.

Workday Adaptive Planning Tips & Tricks: How to Create a Dimension

February 8, 2023 by Ben Alcock

Dimensions are a key element of Workday Adaptive Planning. Creating a dimension is very easy. Here are some approaches to create dimensions in Workday Adaptive Planning.

The first thing to consider is hierarchy. It is Revelwood’s best practice to keep dimensions flat, meaning with no hierarchy at all, and leave that to dimension attributes. (I will cover more in detail in an upcoming blog post.) We like to keep them flat in order to tag them with dimension attributes. This is very useful when further breaking down dimensions by different categories. If we need dimensions to be in a hierarchy, we build that structure as attributes and keep dimensions flat at the leaf most level.

To add a dimension in Adaptive Planning, select the three lines and navigate to modeling and click Dimensions. At the top of the screen notice this little Create new dimension button.

Click it and give your new dimension a name and unique code.

Now from here there’s a couple ways to add values to your new dimension.

The first is to add it manually. Select your new dimension and click this icon next to the previous create new dimension button, the Create new dimension value button.

From here, give each dimension value a name and a unique code. This could take a while, which is why I recommend one of the next two options instead of the manual process.

The second possible way is via an import. First we need an import template. Click this Import dimension structure button

and download an import template. Open it, and in column A on the first row only, write the name of your overall dimension.

In the next row in column F and G, include the unique dimension value code and name from your data source.

Save and return to Adaptive Planning. Choose your new file and click import. If all goes well you’ve saved a ton of time populating your new dimension.

The third choice is by far the easiest and fastest method. To import dimension values select the check box Data import automatically creates new dimension values:

The parent dimension is already created. Tagging data you’re going to import with a nonexistent dimension value will cause Adaptive Planning to automatically populate your new dimension.

And that’s it for creating dimensions!

Invaio Sciences Sows the Seeds of FP&A Transformation with Workday Adaptive Planning

January 25, 2023 by Revelwood

How do you reduce errors and increase accuracy in financial planning, forecasting and reporting? With Workday Adaptive Planning.

Invaio Sciences, Inc., a Flagship Pioneering company, is headquartered in Cambridge, MA. Invaio is striving to accelerate the leap to a more nature-positive era for agriculture by solving performance and delivery challenges of traditional chemical and biological crop protection.

Until recently, Invaio was using Microsoft Excel for its budgeting, planning, forecasting and reporting needs. The manual nature of Excel – and the human error that comes with it – became too much of a burden for the company.

Invaio is a privately held company that relies on private investments and debt financing until it becomes cash positive. The finance team needs to provide senior management with updated and accurate numbers to help management determine when the company needs to raise more money.

Invaio selected Workday Adaptive Planning with Revelwood as its implementation partner. “A personal connection referred Revelwood to us,” said Ryan Garceau, head of operations & finance at Invaio. “As soon as we met the Revelwood team there was an instant rapport. We’ve since developed a great relationship between the Invaio finance team and the Revelwood team members. The Revelwood team was committed to making this project a big success.”

With its new Workday Adaptive Planning solution, Invaio now has a new approach to managing foreign transactions and financial reporting. “The way we do financial planning, forecasting and reporting are like night and day,” added Garceau. “We’ve gained efficiencies across the board, and we’ve changed the finance team’s value proposition. Our activities are no longer about building the reports, they are about analyzing the data.”

Interested in learning the full story? Read the success story to learn how Invaio benefits from Workday Adaptive Planning.

Texans Credit Union Adapts to Market Fluctuations with Workday Adaptive Planning

January 11, 2023 by Revelwood

How do you provide budgeting and forecasting answers on-the-fly in real-time with spreadsheets? You don’t. This is why Texans Credit Union switched from spreadsheets to Workday Adaptive Planning for streamlined and more efficient budgeting, forecasting and reporting.

Texans Credit Union, founded in 1953 by 11 Texas Instruments employees, now serves over 117,000 members. Its mission is to improve the well-being of all Texans. The organization is a full-service, not-for-profit institution with members throughout the Dallas Fort Worth area.

“A budget built on a series of spreadsheets was not appropriate for a business of our size and complexity,” said Ben Hart, CFO, Texans Credit Union. “Our biggest challenge was that we couldn’t easily pivot and change things on-the-fly.”

Texans Credit Union partnered with Revelwood to build a Workday Adaptive Planning-based budgeting and planning model that included personnel, CapEx and revenue. The data flows into a dashboard for easy visualization.

The budgeting and planning model enables Texans to see actuals at a department level and branch level. “Adaptive is a very intuitive and flexible solution,” stated Hart. “Revelwood was very creative in how they implemented Adaptive for Texans. We had weekly meetings with Revelwood where they would share their knowledge and even go through technical details with us.”

Interested in learning the full story? Read the success story to learn how Texans benefits from Workday Adaptive Planning.

Workday Adaptive Planning Tips & Tricks: Crosstabs – The Significance of and How to Build

October 12, 2022 by Alex Goldman

Crosstabs in Workday Adaptive Planning are a great way to visualize and analyze data. This type of chart is great for comparing data sets and their relationships. Data in a Crosstab is visualized in a tabular format. Crosstab allows you to compare change over time, based on one or more accounts. Crosstabs also allow you to add different dimensions to view variances throughout the company. For example, a Crosstab can display a company’s revenue and net profit from different years, as well as filter by office or location.

How to Build a Crosstab

There are a few rules to follow in order to successfully build a Crosstab. At least one Account is needed to be selected to build a Crosstab. While Time is typically used in Crosstabs, it is not required when making the visualization. The Time Dimension will allow you to compare data results from previous time periods, which can allow businesses to analyze whether they are going in the right direction. Here is a step-by-step development of a Crosstab to view Expenses for a company on a quarterly basis and by Supervisor Organization (Operations, Corporate, or Other).

The First Step is selecting the Crosstab option and dragging it onto the plain space. Then we can edit the chart name. This is done by selecting the A in the right corner of your screen and then entering the name that you would like to use. For the sake of this example, the name used is “Expense Crosstab.”

Graphical user interface, application, Word

Description automatically generated

Graphical user interface, application, box and whisker chart

Description automatically generated

Graphical user interface, text, application

Description automatically generated

The next step is adding the required accounts and dimensions. In this chart, Columns will be just time, while rows will be both Account and Supervisory Organization. To do this, click the stack of coins below the A on the right side of the screen. Under Columns, select Time, and under rows: Accounts 6100 Payroll, 6200 Taxes and Benefits, 6300 Office Expenses, 6400 Travel and Entertainment, 6500 Marketing, 6999 Other Expenses. You will also Add Supervisory Organization to rows as well.

Graphical user interface, application

Description automatically generated

This is what the account will look like:

Graphical user interface, application, Teams

Description automatically generated

You can edit the time to be monthly, quarterly, or yearly. You can do this in the bottom right corner where it says “Quarter” by clicking and changing it to month or year. As of now, we have just one time period. Let’s make the dashboard a time range with multiple data points. To do this we will click the line with two small circles.

Now that we have selected this, you can select the time range you would like your Crosstab to display. To adjust the time, click on the time period and make your selection.

For this example, the range Q1 2021 to Q1 2022 was selected. Below is the final product for the Crosstab.

As shown, the Crosstab is a simple but effective way to paint and visualize numbers that any company can use for an easy way to track and analyze finances.

Workday Adaptive Planning Tips & Tricks: Data Organization with Microsoft Power Query

What is Microsoft Power Query?

Benefits of Using Microsoft Power Query

Data Organization with Power Query

Read more Workday Adaptive Planning Tips & Tricks:

Workday Adaptive Planning Tips & Tricks: Restrictions on Cube Sheets

Read more Workday Adaptive Planning Tips & Tricks:

Workday Adaptive Planning Tips & Tricks: Metadata Loaders

Read more Workday Adaptive Planning Tips & Tricks:

Workday Adaptive Planning Tips & Tricks: How to Create Dimension Attributes

Read more Workday Adaptive Planning Tips & Tricks:

Apex Entertainment Performs Enterprise Planning with Workday Adaptive Planning

Read more blog posts on Workday Adaptive Planning:

Workday Adaptive Planning Tips & Tricks: How to Create a Dimension

Read more Workday Adaptive Planning Tips & Tricks:

Invaio Sciences Sows the Seeds of FP&A Transformation with Workday Adaptive Planning

Read more blog posts on Workday Adaptive Planning:

Texans Credit Union Adapts to Market Fluctuations with Workday Adaptive Planning

Read more blog posts on Workday Adaptive Planning:

Workday Adaptive Planning Tips & Tricks: Crosstabs – The Significance of and How to Build

How to Build a Crosstab

Read more Workday Adaptive Planning Tips & Tricks:

Revelwood Overview

EXPERTISE

ABOUT

CONNECT

Adaptive Planning

What is Microsoft Power Query?

Benefits of Using Microsoft Power Query

Data Organization with Power Query

Read more Workday Adaptive Planning Tips & Tricks:

Read more Workday Adaptive Planning Tips & Tricks:

Read more Workday Adaptive Planning Tips & Tricks:

Read more Workday Adaptive Planning Tips & Tricks:

Read more blog posts on Workday Adaptive Planning:

Read more Workday Adaptive Planning Tips & Tricks:

Read more blog posts on Workday Adaptive Planning:

Read more blog posts on Workday Adaptive Planning:

How to Build a Crosstab

Read more Workday Adaptive Planning Tips & Tricks:

Footer

Revelwood Overview

EXPERTISE

ABOUT

CONNECT