What is Get & Transform (Power Query) in Excel?

Get & Transform (formerly known as Power Query) is perhaps the biggest hidden secret in Excel and the tool that can save you hours and hours of boring and repetitive work. To analyse data in Excel, in many cases you must first import it. This can be done manually or use VBA, but manually importing data into Excel is slow, tedious and error-prone and VBA requires some programming knowledge.

Get & Transform allows the automation of the process of importing data, but it requires no coding knowledge and tends to result in better performance. But it doesn’t only import data; Excel Get & Transform is an advanced ETL tool.  ETL is an acronym for Extract, Transform and Load.

It allows you to:

  • Extract: Most data that is analysed in Excel is imported from an external data source. Extract means moving this data from the external data source into the Get & Transform tool. This can include Excel files, Text or CSV files, Databases such as Microsoft SQL Server, Microsoft Access and Microsoft Exchange, Facebook and even web pages.
  • Transform: Extracted data often is often in a form that isn’t easily analysed. There may be unwanted columns, bad formatting or corrupted data. Get & Transform has a vast array of features that enable you to clean your data before loading to Excel table. The extracted data can be transformed in almost unlimited ways:
    • Removing columns
    • Filtering the data
    • Grouping the data,
    • Pivoting/unpivoting the data
    • Splitting up text
    • Extracting keywords from text
    • Add rows from another table
    • Remove Duplicate Records
    • Split a Column the Number of Chars
    • Duplicate Columns
    • Use First Row as Headers
    • Remove Columns
    • Remove Duplicates
    • Fill Down Values
    • Create Index Columns
    • Remove Rows With Errors
    • Group Rows and Get Counts
    • Reverse Rows
    • Transpose
    • Replace Values
    • Split First & Last Name
    • Consolidate Excel Workbooks or Worksheets
    • Load: The transformed data from the Get & Transform tool is loaded into an Excel table or into an Excel Pivot Table, where it can be analysed and shared.

    Whatever you have done, the whole transformation of the data process can then be saved as a Query. Perhaps the greatest and most time-saving feature of Get & Transform is that it allows you to set up a query once and then reuse it with a simple refresh. Get & Transform is very easy to use and pick up, more so than other Excel tools like formulas or VBA. And no coding knowledge is required. Get & Transform records all your transformations step by step. You can even go back and amend any of the steps individually.

    Get & Transform is part of Infero’s new Data Analysis and Visualisation with Microsoft Excel Course. Contact us to learn more about it. The applications for your own work in Excel could be endless.

    National Picnic Month

    As July is National Picnic Month, it encourages you to get out and about  and celebrate the warm summer days outdoors while enjoying some great food.

    Throughout the history of civilization, there has always been something special about leaving  your home and eating a meal outdoors. We know there’s a beautiful world out there waiting for us to come and visit it, and when summer comes around, we all can’t wait to go out  and spend time outside.

    Picnics have been used for many things throughout the centuries, from a simple family gathering spent enjoying the outdoors, to a peaceful protest in the  famous Pan-European Picnic, which was a peace demonstration held on the Austrian-Hungarian border near Sopron, Hungary on 19 August 1989.

    Picnic Month celebrates the history of this activity and the way it has been used to bring families, countries, and all humanity together.

    Picnic month is exceptionally easy to celebrate! All you need to do is just  to get outside and enjoy all the wonderful sights together with delicious food, which tastes much better outdoors.

    Get your family and friends together at a nearby park and bring your favourite foods that can be eaten cold for a traditional picnic or bring a small grill and cook some delicious food on the spot.

    Get the Children Involved

    As summer arrives, a lot of parents may feel a little uneasy and not sure  how to keep their kids entertained.

    A picnic is a perfectly easy, and  no fuss  activity to add some excitement  to the long summer days.

    Whether you are a parent, carer or teacher, picnics are an ideal opportunity for learning and development. Simple tasks, such as buttering a slice of bread, or wrapping food and cutting up food can benefit children and contribute to their growth and further their skills

    Positive Impact on Mental Health and Wellbeing

    Science proved that nature and spending time outside has a positive effect on mental health and wellbeing. Nature has a calming influence and provides a longing and much-needed escape from the everyday pressures. 

    Planning a picnic and having something to look forward to also has a fantastic impact on mental health. The social interaction which is a part of picnics has also a positive influence on mental health and wellbeing as humans tend to  thrive on positive, real communications with others.

    In 2021, picnics are still a much-loved and popular pastime which provide a welcome relief from the everyday routine.

    Microsoft Excel: Dashboards Course

    Excel dashboards are one-page (or rather one worksheet) summaries that contains important information which allows managers to track key KPIs, metrics and other data points in one visual, central place. They may be used in almost any industry, for almost any purpose. It is possible to have a Dashboard to track finances, for marketing or to show the progress of a key project.

    Dashboards are made up of tables, charts, gauges and other visualisations. Able to display a high-level view of an organisation, a good dashboard will aid in making business decisions and can also provide a portal to keep everyone up to date, giving a real time view of current status or performance.

    If you want to be able to extract useful, actionable organisational information from your raw data, and present it in a visually compelling format that will enable key trends to be seen and decisions to be made, then Infero Training’s Microsoft Excel: Dashboards Course will help you do this. 

    Creating an Excel Dashboard is a multi-step process and our course takes you through all of these in an easy-to-follow manner. As well as all of the technical aspects, the course covers the key things you need to keep in mind when creating a dashboard and how you need to be clear about the objectives of the dashboard, before you even start using Excel.

    The course also looks at simple Excel dashboard design rules, such as the simple use of colours, easily read, clean design and other considerations.

    Excel Dashboard design should be:

    • Understandable with no confusion over what each of the metrics mean.
    • Interactive so that the Dashboard can be changed for different areas of an organisation, or to answer different questions.
    • Clearly labelled, with all of the charts and tables on the dashboard displaying a clear message.
    • Organised into a symmetrical grid that aligns sections into blocks of data.

    Course Content

     

    Lesson 1: Data Analysis Fundamentals

    Topic A: Introduction to Data Science

    Topic B: Create and Modify Tables

    Topic C: Sort and Filter Data

     

    Lesson 2: Visualizing Data with Excel

    Topic A: Visualize Data with Charts

    Topic B: Modify and Format Charts

     

    Lesson 3: Analysing Data with Formulas and Functions

    Topic A: Analyse Data with Formulas and Named Ranges

    Topic B: Analyse Data with Functions

     

    Lesson 4: Analysing Data with PivotTables

    Topic A: Create a PivotTable

    Topic B: Analyse PivotTable Data

     

    Lesson 5: Presenting Visual Insights with Dashboards in Excel

    Topic A: Visualize Data with PivotCharts

    Topic B: Filter Data Using Slicers and Timelines

    Topic C: Create a Dashboard in Excel

    To learn more about this course, contact us today.

    Use MS Outlook to Delay or Schedule Emails

    It is possible to delay the delivery of an individual message or messages using Microsoft Outlook, after you have clicked Send.

    To delay the delivery of an individual message, follow these steps:

    1. When you have composed the message, on the Message Tab select the Launch Dialog-Box arrow in the bottom right of the Tags group.



    1. In the Dialog-Box that appears, in the Delivery options section, tick the Do not deliver before check box, and then choose the date and time when you want your message to be delivered.
    1. Close the Dialog-Box.
    1. Once you’ve finished composing your email message, click Send.

    The message will remain in Outlook’s Outbox until your chosen delivery time.

    If you later decide you want to send the message earlier than scheduled time, follow these steps:

    1. Locate the Outbox folder, in the Folders Pane at the left-hand side of the Outlook window and double-click it to view any messages it contains.

    2. Find and open the delayed message.

    3. On the Message Tab select the Launch Dialog-Box arrow in the bottom right of the Tags group.

    4. In the Dialog-Box that appears, in the Delivery options section, untick the Do not deliver before check box

    5. Close the dialog box and click Send.

    NB: For this feature to work, Outlook must be online and connected.

    There are many other options that you can use in Outlook, including setting a rule to delay all messages being sent by a specific amount of time. Contact us to find out more about our Outlook courses at all levels.

    Yorkshire Day

    Yorkshire Day is a yearly celebration on the 1st August to promote the historic English county of Yorkshire.  Initially marked in 1975, the Yorkshire Ridings Society started the day as “a protest movement against the local government re-organisation of 1974”. The date was not chosen at random, but has historical significance being the anniversary of the emancipation of slaves in the British Empire in 1834. It was William Wilberforce, a Yorkshire MP, who had been one of the campaigners for this emancipation.

    Many people travel to Yorkshire on Yorkshire Day to enjoy the good food, the amazing attractions and the incredible scenery of England’s biggest county and, after months of being shut, many Yorkshire attractions will be hopefully be open or re-opening on August 1st

    Events

    Tapton Hall  – Tapton Hall in Sheffield has usually celebrated this holiday with traditional music from local bands and a themed afternoon tea. Hopefully something similar will occur this year

    There are sometimes Yorkshire Day Chocolate Workshops in Scarborough. This may happen again this year, but please check closer to the time. – What would Yorkshire Day be without a chocolate fountain or at least a proper Yorkshire Tea Cup.

    Barnsley Museums – A number of museums and historic sites in around Barnsley. There are usually a number of Yorkshire Day activities planned. In the past these have including a Yorkshire pudding competition, a digital jigsaw and a Yorkshire sonnet, and more. Keep an eye on the website. 

    Attractions

    York Castle Museum – This museum covers hundreds of years of York’s social history in one place. Check on the Museum’s website closer to the date.

    The Hepworth Wakefield – The Hepworth Wakefield is an award-winning art gallery in the heart of Yorkshire, set within Wakefield’s historic waterfront overlooking the River Calder. 

    North Yorkshire Moors Railway – A preserved historic railway stretching for 18 miles through the heart of the North York Moors National Park. It is one of the largest preserved heritage railway in the UK.

    York Art Gallery – Housed in an impressive historic building with an impressive collection, providing tickets are purchased in advance online. 

    Rievaulx Abbey – Set in a valley in the North York Moors National Park with the impressive ruins of one of England’s most powerful Cistercian monasteries. The Abbey has a new visitor experience which looks at Rievaulx Abbey’s fascinating past. There is also a museum which will hopefully be open.

    York itself is a great city to visit, with many attractions including the Jorvik Viking Centre. And of course, there are amazing walks in Yorkshire, including on the famous Ilkley Moor, with or without a hat.

    Enjoy Yorkshire Day.

    Creativity and Innovation Course

    Perhaps we don’t often think about being creative at work, but it isn’t just limited to the arts or sciences; creativity is extremely important to business. Using creativity properly can find new solutions to problems, foster teamwork, generate higher revenues and help deal with change. More and more research shows the tangible benefits of creativity in an organisation.

    Is your workplace a creative one? There are many examples of creativity at work:

    In Leadership. – A creative manager promotes creative thinking in employees and takes novel approaches to interactions and work. 

    In Problem Solving – Taking an alternative method of approaching issues, a creative problem-solver will use a different kind of thinking to develop different kinds of solutions.

    In Project Management – Being creative in project management can transform a company’s ability to complete quality projects on time, by using ‘big-picture’ thinking. 

    And other areas such as marketing, sales and production can benefit from the correct use of creativity. The creative touch will be transforming at every level of your business. Creativity is a good thing, but, better still, it’s something anyone can learn and utilise.

    Infero’s Creativity and Innovation course teaches students about creative thinking in the workplace. Students will develop creative thinking skills, mentally and physically prepare for creativity, incorporate innocence, intuition, and adventure into creative thinking, and use creativity to generate ideas and solve problems. The course describes exercises that can be used to prepare the mind and body for creative thinking and explains the most common ways creativity is incorporated into problem solving. Course activities also examine recruiting and retaining creative individuals, performing a creativity audit, communicating with creative people, defining creative rhythm, and exploring ways to implement a creative culture. Students also learn how to organize creative teams, conduct effective team sessions, avoid roadblocks to team creativity, increase creativity in a team, and use the creative problem-solving process. The manual is designed for quick scanning in the classroom and filled with interactive exercises that help ensure student success.

    Delivery Method

    Instructor led, group-paced, online or classroom-delivery learning model with structured hands-on activities.

    Target Students

    This course is for people thinking about creatively in the workplace.

    Prerequisites

    There are no prerequisites for this course.

    Course Objectives

    Course activities examine recruiting and retaining creative individuals, performing a creativity audit, communicating with creative people, defining creative rhythm, and exploring ways to implement a creative culture.

    Course Content

    Unit 1: Creative thinking basics

    Topic A: Creative thinking

    Topic B: The creative process

    Unit 2: Personal creativity

    Topic A: Preparing to be creative

    Topic B: Increasing creativity

    Unit 3: Creativity in organizations

    Topic A: Creative organizations

    Topic B: Using creativity

    Unit 4: Fostering a creative environment

    Topic A: Employing creative individuals

    Topic B: Maintaining a creative environment

    Unit 5: Promoting team creativity

    Topic A: Organizing creative teams

    Topic B: Conducting team sessions

    Topic C: Promoting and using creativity

    Contact us to find out more about this course.

    25 Ways to Be Creative

    The BBC Arts Great British Creativity Test suggested that being creative is good for you and can help avoid stress and improve self-development. Multiple past studies confirm that being creative can increase positive emotions, decrease anxiety, and even improve immune system functioning. And more, a study published in the Journal of Occupational and Organizational Psychology said that engaging in creative activities outside of the office can result in better work performance.

    “You Can’t Use Up Creativity. The More You Use, The More You Have”

    – Maya Angelou

    Being creative doesn’t have to be hard and if you think you are not creative, it is just not true.

    “If someone tells you they cannot read and write, you don’t assume that they are not capable of reading and writing, but that they have not been taught. It is the same with creativity. When people say to me that they are not creative, I assume they just haven’t learned what’s involved.” –  Ken Robinson 

    There are countless creative things that you can do, some achievable very easily (although some take a little more work, obviously); some that you can do right now and some that can be accomplished with things you already have. Here are twenty-five ideas to get you started:

    1. Write a Song. Pen just the lyrics, or just the music or both.
    2. Make Origami. There is plenty of help and ideas here.
    3. Write A Short Story. Fiction. Nonfiction. Romance, western, sci-fi, contemporary. Whatever.
    4. Draw A Picture. Use Crayons, paint, chalk, pencil, pens, or whatever you have to hand. You can draw digitally to on a tablet or laptop.
    5. Work on a Book. Novel, nonfiction, whatever and whenever you want. It can be about anything you want it to be.
    6. Get creative in your Garden with Flowers and Vegetables.
    7. Write Some Poem. Poetry can be almost anything. Here’s one list of nearly 200 different forms of poetry.
    8. Take Some Photos. Go to a park or some other interesting place near to you.
    9. Build A Website. It can be on anything. Just Google ‘how to create a website’ for almost endless help.
    10. Create a Playlist. Video or music. Or both. It’s up to you. Have different playlists for different moods.
    11. Upcycle a Piece of Furniture. Get it from a market or from your own home. Change that old piece of kit into something spectacular
    12. Get an Adult Colouring book. You can be incredibly creative just colouring in. You can even download pages to print out and colour.
    13. Rearrange Your Home or Paint and Decorate. Your project can be small or large.
    14. Try Candlemaking.
    15. Make a Video. We all have smartphones with the ability to take high quality video that we never use. There are plenty of free apps for video editing Try Shotcut to start off with
    16. What about Knitting, Needlework, Quilting or Crochet?  
    17. Create a New Recipe and Get Creative in The Kitchen. The TV schedules are full of cookery shows, so there are plenty of ideas. 
    18. Create a Photo Album. If you have a lot of photos hanging around, then buy an album and organise them. Or you can put those hundreds of digital photos into an online Photo Album
    19. Start a Journal and write about anything and everything.
    20. Buy a Model Kit and put it together and paint it.
    21. Design Some Clothes or a Bag or Shoes. Or customise some you already have.
    22. Make Some Recycled Art. Turn something you don’t use anymore into something great. It doesn’t have to cost a lot.
    23. Restore Something. If you have an item that is broken or past it’s best, do a little research and get it back to where it was.
    24. Research a Subject that you have always been interested in. You can use MS OneNote to organise what you find. (We have and upcoming blog on OneNote.)
    25. Practice an Instrument. If you ever once started to learn an instrument and still have it lying around, then pick it up and practice a little.

    “Creativity is Intelligence Having Fun!”​

    – Albert Einstein

    And there are lots more things you can try. Go on – get creative!

    Amazing Things You Never Knew About MS OneNote

    OneNote is a part of Microsoft Office and like Evernote can be used for taking notes and storing information, making it perfect for research and other projects.

    It allows the creation of notebooks for different areas and topics; each of these notebooks can then be divided into sections and pages. The basic structure then is Notebook > Section > Pages. However, pages can also have subpages.  And subpages can also have subpages, which allows for many levels to arrange your information in. It is also possible to have multiple notebooks in OneNote, each dedicated to a different topic.

    When you have created a notebook, a section and put in your first page, you will be able to type notes in, just as in any word processor. OneNote offers the same tools as Word in its ribbon: font, text style, bullets and numbering, highlighting etc. Tables can be created and it is possible to insert images, photos, audio, and video. Audio can also be recorded. Images that appear within notes can be enlarged, shrunk, and cropped. It is possible to create links or paste in URLs from web browsers. 

    OneNote differs from Word and other word-processors in one crucial respect. Its pages act more like a pinboard than a Word document. Every piece of content put into a page is placed in its own field or box. Boxes can be re-sized or dragged to change their position on the page.

    If you use OneNote on a tablet or a touchscreen laptop, you can also draw, sketch, and write notes freehand. It is even possible to get text from an image and paste it right into the note (or anywhere else). This is one of OneNote’s best and surprising little-known features.

    OneNote notebooks synced to OneDrive can be opened on any device connected to your Microsoft Office account, be it a phone, tablet, PC, or laptop.

    Microsoft also provides a web clipper browser extension that allows the copying of content from a webpage directly into OneNote, eliminating the need to cut and paste. Ads and other unwanted er page elements can be stripped out and there are options to clip the whole page or just a specific section.

    The best thing about OneNote is that it is pretty much free. It comes as standard with all Office installations, but even if you haven’t got one of these it can be downloaded for free on Android, iOS, macOS and Windows, or accessed as part of an Office Online Account.

    If you want to learn how to use OneNote effectively, Infero Training has a course and you can find the details here.

    World Watercolour Month

    World Watercolour Month is celebration of the medium of watercolour painting, encouraging everyone to take up a brush and give this form of painting a try. Not only that, it is an event which encourages and supports arts education.

    Watercolour is a painting method in which the paints are made of pigments suspended in a water-based solution and the term ‘watercolour’ refers to both the medium and the artwork itself. It’s an extremely ancient form of painting, dating perhaps to the cave paintings of palaeolithic Europe. It has been used for manuscript illustration since at least Egyptian times but especially in the European Middle Ages.

    World watercolour month was founded by Charlie O’Shields, creator of the Doodlewash, a website dedicated to all things watercolour. The event Partners with the Dreaming Zebra Foundation which provides help to those who may otherwise not be able to develop their artistic creativity.  They are a non-profit organisation that encourage children’s individuality, help them to express themselves creatively, and to follow their dreams within art. The aim is to raise awareness and to raise funds for this worthwhile cause.

    Dreaming Zebra also have an:

    “..art recycling program that is free to the public. Reusable art & music supplies that would otherwise be discarded, along with new or unsold materials, are donated by individuals and businesses and matched to recipients who have requested those materials for arts education purposes in the community.”

    Art is important to us all. Many would argue that it absolutely essential. It has always played an important part in education, being a great leveller and being one of the things that everyone has access to and that everyone can take part in. It’s for this reason that it is so terrible that it is often the case that art classes are first in line when budget cuts are made in education.

    During World Watercolour month you can take the ultimate challenge of painting 31 watercolours in 31 days. Even if you are not up to that, you can dust off your old palette and have a go. Watercolour paints are available very cheaply in many shops and also online, so there is not excuse. If you need tips, try here.

    You can post your masterpiece online with the hashtag #WorldWatercolorMonth. And, if you can, donate to The Dreaming Zebra Foundation to help children across the world reach their artistic potential.

    Happy painting!

    Managing Organisational Goals

    The goals of an organisation are vitally important. They are the objectives established by that company to define expected outcomes and to guide employees’ efforts. Organisational goals help define a company’s purpose, assist its business growth and help achieve its financial objectives. The setting specific organizational goals will, by definition, help a company to measure the progress of the organisation.

    Goals should be specific, measurable, achievable and timely. Goal setting, when done well, has many benefits:

    • Motivates Employees
    • Aids the Prioritising of Work
    • Facilitates Decision Making
    • Enhances Teamwork
    • Provides a Metric to Measure Success
    • Helps Guides Employees
    • Enhances Time Management

    Considering the importance of goal setting, we should perhaps spend more time on it than we do.

    Managing Organisational Goals Course

    As a manager, you are responsible for setting the goals for your team and for managing their work. Organisational goals can often get lost in layers of management and not get communicated to the individual contributors. In the absence of goals, employees can feel unmotivated or can even set their own goals for achievement, which will result in poor and conflicting team performance. In this course, you will develop skills needed to establish tactical goals for your team based on organisational directives and general goals from your manager and create and manage action plans to achieve these goals.

    Delivery Method

    Instructor led, group-paced, online or classroom-delivery learning model with structured hands-on activities.

    Target Students

    This course is intended for the professional employee who is a team leader.

    Prerequisites

    Prior to taking this course, the student should have knowledge of corporate goals. Other courses that may be helpful are:

    – What Good Managers Do – The First 100 Days

    – Negotiating Skills

    – Delegating

    Course Objectives

    Upon successful completion of this course, students will be able to:

    • Refine goals for your team.
    • Develop action plans and contingency plans for achieving your team goals.
    • Manage your team and work with stakeholders in your organisation to achieve the action plan.

    Course Content

    Lesson 1: Establishing Team Goals

    Topic 1A: Clarify General Team Goals

    Topic 1B: Create a Goal Map

    Topic 1C: Translate General Goals into Tactical Goals

    Lesson 2: Developing a Team Plan

    Topic 2A: Create Action Plans for Achieving Goals

    Topic 2B: Develop a Contingency Plan

    Lesson 3: Achieving the Team Plan

    Topic 3A: Implement the Action Plan

    Topic 3B: Evaluate Success

    To find our more about this course and many other business courses, contact us here at Infero.