Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Closed Thread
 
Thread Tools Rate Thread Display Modes
Old 08-28-2019, 07:49 AM   #1
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,248
Thanks: 115
Thanked 3,075 Times in 2,793 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Normalise Spreadsheet Data Easily For Access Using Excel Power Query

When faced with normalising spreadsheet data for use in Access, various solutions are available often involving complex user defined functions.

By contrast, a very simple approach was recently brought to my attention by long time AWF member alansidman in post #9 of this thread at another forum Converting horizontal Excel data with monthly periods into vertical Access database records

The solution involves the use of Excel Power Query which is built into Excel 2013 and later and available as an add-in for Excel 2010.
In the latest versions of Excel the feature has been renamed as Get & Transform but the functionality is broadly the same.

Power Query is an immensely powerful tool which really should be included with Access. Despite significant online pressure in the Access User Forum, MS have so far ignored many requests over several years to add it to Access.

I am definitely no expert in its use & have barely scratched the surface of what it can do. In fact, I had to ask help from alansidman to understand how it can be used for this purpose.

On the basis that others may also be unaware of its use, here is a brief explanation with 3 Excel files as examples.
Each file contains 2 worksheets and a query
• Sheet1 – original data in crosstab type format
• Sheet2 – ‘normalised’ data suitable for importing to Access
• Query1 – converts Sheet1 to Sheet2
In the simplest of these, (NormaliseDataExample.xlsx), the original data in Sheet1 is



The query used to convert the data is
PHP Code:
let
    Source 
Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Part Number", "Part Description"}, "MonthYear", "Quantity")
in
    
#"Unpivoted Other Columns" 
Table1 is the name given to the range A1: J6 containing data for conversion
PartNumber & PartDescription are the fields that we want to remain unchanged
The monthly data is then converted into 2 columns which will be named MonthYear and Quantity

The conversion is very fast – a fraction of a second.
Below is part of the converted data in Sheet2



To convert this yourself, open PowerQuery by clicking the Data tab.
Next click Get Data …From Other Sources…Blank Query.
Click Advanced Editor and enter the above query code in the query window.
Click Done and the converted data is displayed.
Click Close and Load to save this as a new worksheet

The other two examples are slightly more complicated but the idea is basically the same
• StudentAttendanceMarks.xlsx
• CurrencyExchangeData.xlsx

NOTE that each of these were taken from Access crosstab queries each based on 2 tables. As a result, the power query output is only partly normalised and needs to be split further into 2 tables.

I hope this method is useful to others
Attached Images
File Type: png Sheet1.PNG (9.9 KB, 154 views)
File Type: png Sheet2.PNG (15.5 KB, 156 views)
Attached Files
File Type: xlsx NormaliseDataExample.xlsx (20.5 KB, 20 views)
File Type: xlsx StudentAttendanceMarks.xlsx (55.8 KB, 9 views)
File Type: xlsx CurrencyExchangeRates.xlsx (22.4 KB, 10 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)

Last edited by isladogs; 09-19-2019 at 12:27 PM. Reason: Fixed typo
isladogs is offline  
The Following 6 Users Say Thank You to isladogs For This Useful Post:
Alansidman (09-06-2019), MickJav (08-30-2019), Minty (08-28-2019), MrHans (08-28-2019), theDBguy (08-28-2019), Uncle Gizmo (08-28-2019)
Closed Thread

Tags
excel , normalise , power query , spreadsheet data

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2016- Loading Power Query into Power Pivot machumpion Excel 3 09-21-2017 01:22 PM
Normalise data from spreadsheet prior to load to Access. hardy1976 General 3 12-17-2013 04:58 AM
Power Tip: Exclude data from a query by using a table HiTechCoach Queries 1 07-22-2010 03:57 PM
Normalize your spreadsheet data Easily. Uncle Gizmo Sample Databases 0 02-19-2008 03:56 PM
to Normalise or not to Normalise - that is the question Dennisk Tables 2 07-01-2007 10:54 PM




All times are GMT -8. The time now is 05:52 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World