Share via

Office 365 Smartsheets Alternative

Anonymous
2021-06-22T14:27:18+00:00

Vague title, I know, but it basically describes what I'd like to ask.

We have a dataset that we use to populate a Power BI report. The dataset is updated on a monthly basis by around 30 different offices within our company. Currently we use Smartsheets to update the data as we can use the workflows function to automatically send webforms out to the relevant staff members containing only the fields that they need to update that month. The Smartsheet contains a date column that is used to trigger the sending of the webforms.

The Smartsheet looks a little like this:

KPI Number Staff Member KPI Name Monthly Data Monthly Target Report Date
1 John Smith Staff Sickness Figures 05/01/2021
2 John Smith Staff Sickness Figures 04/02/2021
3 John Smith Staff Sickness Figures 04/03/2021
4 Dave Jones Remaining Budget 05/01/2021
5 Dave Jones Remaining Budget 04/02/2021

The system works like this:

  1. Each month, 5 days before the date in the Report Date column a webform is automatically sent out to the staff member in the Staff member column. The webform contains all the above fields (excluding the Report Date column);
  2. Each staff member completes the Monthly Data and Monthly Target fields and hits the Submit button;
  3. The Smartsheet is automatically updated with the relevant data for the relevant month;
  4. The Power BI report then pulls the new data through.

So, data sent through for January might look like this:

KPI Number Staff Member KPI Name Monthly Data Monthly Target Report Date
1 John Smith Staff Sickness Figures 78 85 05/01/2021
2 John Smith Staff Sickness Figures 04/02/2021
3 John Smith Staff Sickness Figures 04/03/2021
4 Dave Jones Remaining Budget £120,000 £55,000 05/01/2021
5 Dave Jones Remaining Budget 04/02/2021

My question is, is there any way to replicate this process using Office 365 apps like Lists and Automate? We use 365, but up until now my knowledge of 365 is limited to the Outlook, Word, and Excel. I've never used the apps.

There are so many apps that I don't even know where to start, and the guidance/help for each that I've found seems to assume that you already know how they work.

Any help would be greatly appreciated.

Microsoft 365 and Office | Install, redeem, activate | For home | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-06-22T15:23:49+00:00

    I don't know what "Smartsheets " is. Like you say, lots of products, hard to keep up with all of them.

    .

    What format is 'smartsheet' data in? Excel spreadsheet? Or something else.

    .

    Where is the data going?

    .

    Sorry, I don't quite understand your flow of data.

    .

    There is good chance the tool you are looking for is called PowerQuery. It is part of Excel (or PowerBI, but don't worry about that for now).  It is a tool that can connect to over 100 different data sources, import data and ouput into a spreadsheet .

    .

    Short form: What is PowerQuery                  Jon Acompora
    .  *  PQ is a Data Automation tool
    .  *  Import data into Excel from several dozen sources
    .  *  Clean data
    .  *  Prepare data for use in Excel
    .  *  Combine/Stack data (Append Queries)
    .  *  Join data (Merge queries)
    .  *  group and summarize (PivotTables)
    .

    !   Power Query Overview: An Introduction to Excel’s Most Powerful Data Tool         2020 05 13           Jon Acampora
    https://www.excelcampus.com/power-tools/power-query-overview/
    https://www.youtube.com/watch?v=sIejxpsbI3A&feature=emb_rel_pause (15min51)
    Learn how this awesome feature of Excel and Power BI called Power Query will help you automate the process of importing, transforming, and cleansing your data to save a TON of time with your job.
    .  *  The Power Query Data Machine
    .  *  Common Data Tasks Made Easy
    .  *  Overview of the Power Query Ribbon
    .  *  Unpivot Data for Pivot Tables
    .  *  Append (Combine) Tables with Power Query
    .  *  Merge Tables – A VLOOKUP Alternative
    .  *  Create Custom Functions
    .  *  PQ Records Your Steps & Automates Processes
    .  *  The Power Query Machine & Power BI

    ! a- Create, edit, and manage connections to external data
    https://support.microsoft.com/en-us/office/create-edit-and-manage-connections-to-external-data-89d44137-f18d-49cf-953d-d22a2eea2d46
    You can use Microsoft Office Excel to create and edit connections to external data sources that are stored in a workbook or in a connection file. By using the Workbook Connections dialog box, you can easily manage these connections, including creating, editing, and deleting them.
    .  *  Learn about data connections
    .  *  Manage connections by using the Workbook Connections dialog box
    .  *  Create an Office Data Connection (ODC) file

    !     Microsoft Power Query for Excel Help (in wiki)
    (Rohn007: This is a VERY good place to start learning about PowerQuery. Just keep digging in to the links)
    https://support.office.com/en-us/article/microsoft-power-query-for-excel-help-2b433a85-ddfb-420b-9cda-fe0e60b82a94
    This is MS home page for PowerQuery help, with links to MANY detailed help pages
    Power Query provides data discovery, data transformation and enrichment for the desktop to the cloud.
    .

    .

    How to easily automate boring Excel tasks with Power Query!            2020 10 14
    https://www.myonlinetraininghub.com/introduction-to-power-query
    What’s the big deal about Power Query? Talk to those who have used it and they’ll tell you how amazing it is. Stories of automating tasks that used to take 3 hours now taking 3 minutes is not uncommon or an exaggeration.
    If you haven’t heard of Excel’s Power Query tool, or you’ve heard of it but you’re not sure if it’ll be useful to you, then check out the video below where I showcase what the fuss is all about.

    https://www.youtube.com/watch?v=L4BuUzccLpo&rel=0      17min
    Power Query can automate the boring and laborious tasks of getting and cleaning data, reducing time spent on these tasks down to the click of a button!
    00:29  How to get PowerQuery 2010-365, PowerBi
    01:15  Why use PowerQuery (time saving)
    02:10  Purpose of PowerQuery
    02:35  Sources PowerQuery can get data from
    03:23  Data Cleaning
    03:43  Example 1: Get data from multiple files in a single folder - Intro
    04:43  How to get data from a folder
    05:50  Transform the data- intro to PQ user interface (starting with data in a single “sample” file)
    06:48  Convert 2 row column headings into single row column headings
    07:30  Split data in a column into multiple columns
    08:28  Add a new column by multiplying 3 existing columns
    09:25  Add a new column by example, removing honorifics from names
    10:17  Add new column to calculate number of days from Order to Shipping
    10:47  Filter data to remove some rows based on value(s)
    11:34  Review recorded Query steps
    11:45  Generalize the “sample file” query to apply to all of the files in the folder
    12:10  Remove Source_name (file) column
    12:18  Change column datatypes (not “formatting”) so Excel knows data types
    13:22  Close and Load data directly to a PivotTable
    14:22  Create a PivotTable
    14:35  Auto Group Order Date
    15:02  Create a Chart from the PivotTable
    15:21  Get new data (new file) – Refresh All
    .
    Easily discover, combine, and refine data for better analysis in Excel..  *  Introduction to Microsoft Power Query for Excel (link to other page)
    .  *  Import data from external data sources (link to other page)
    .  *  Shape data from multiple data sources (link to other page)
    .  *  Filter, sort, and group data (content on this page)
    .

    1 person found this answer helpful.
    0 comments