UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Normalising Data In A Table Via Union Query, Access 2010    
 
   
MadPiet
post Apr 11 2020, 01:27 PM
Post#21



Posts: 3,701
Joined: 27-February 09



Turns out, Excel 2010 can use a folder as a source. First things first though... Here's the download page to get PowerQuery for that:
https://www.microsoft.com/en-us/download/de...dd-31a90cb423e4
,
Then you can use the Folder source, unpivot all the columns after the 7th or so and you'll end up with I think 8 columns of properly normalized data. Then if you want you can import that into Access or just leave it in Excel and do the analysis there. Unless it was my absolutely last option, I wouldn't try unpivoting this in Access. SQL Server sure, Excel sure. Access never.
Go to the top of the page
 
ChrisLayton9
post Apr 14 2020, 03:23 AM
Post#22



Posts: 11
Joined: 3-April 20



Hi MadPiet

Thank you once again for all the help.

I am going about now trying to get Power Query (request with work IT dept).

I may be back to ask how you achieve the end result in Power Query if I can get it sorted today

Thanks again
Go to the top of the page
 
MadPiet
post Apr 14 2020, 11:03 AM
Post#23



Posts: 3,701
Joined: 27-February 09



Chris,

Holler back when you get it installed. If you have to deal with really messy data all the time, you might want to check out Gil Raviv's book. (I think if you want both the digital and the paper version, you can get them cheaper if you buy from the MSFT site). Collect, Combine, and Transform Data Using Power Query in Excel and Power BI (Business Skills) I have it. Absolutely worth it! He deals with all kinds of messy data scenarios and shows/explains how to use Power Query to fix up the data.

If you want, I can send you my dummy file.

Pieter

Go to the top of the page
 
MadPiet
post Apr 15 2020, 09:31 PM
Post#24



Posts: 3,701
Joined: 27-February 09



I'm sooo close. There's one weird error that I haven't figured out, but here's the latest attempt at getting this stuff unpivoted and fixed in Excel PowerQuery..
How do you know which tab to get the data from? Is there only one per file?

I think you should be able to open this...
Attached File(s)
Attached File  Chris_Layton_Attempt_1.zip ( 17.81K )Number of downloads: 1
 
Go to the top of the page
 
ChrisLayton9
post Apr 16 2020, 11:39 AM
Post#25



Posts: 11
Joined: 3-April 20



Hi Pieter

Thanks for the recommendation - I will have a look into this.

Still awaiting the software at the moment frown.gif

Yes so each report when it arrives will have one tab - I can make sure the tabs in each report are named the exact same thing if that helps. I think it defaults to Report_Result or something like that

Chris
Go to the top of the page
 
MadPiet
post Apr 16 2020, 12:39 PM
Post#26



Posts: 3,701
Joined: 27-February 09



Oh, okay... that'll help, I think. I have to work out one weird bug with my code, but I think I have it working properly. Any chance you can provide one more file? I just want to make sure it works with more than one with your data structure.
Go to the top of the page
 
MadPiet
post Apr 16 2020, 08:35 PM
Post#27



Posts: 3,701
Joined: 27-February 09



Silly me! If I connect to the SHEET, and not the TABLE, it works great. I can just format the dates as dd/mm/yyyy or whatever (just specify the locale), and that takes care of the date problem. Then if I do something really radical, like connect to the SHEETs in the Workbooks and not the TABLES, the combine works a champ. I can't imagine it taking terribly long to crank through a folder full of files.

Last question (for now):
When I unpivot all this stuff, I get a bunch of "Infotype nnnn - eventName" or something like that, for example "Infotype 0001 - Screen Saved". Do you want to split that up too? (I mean, while I'm at it... It's stupid easy anyway....)

Here's the final output from the transforms. I just copied it to a new workbook so I could leave all the code behind. Does this output look right? (see attached)

There's some obvious dummy data in there... I just needed to make up a few extra files. But if the structure is right, I think you're gonna be set.

Pieter

Attached File(s)
Attached File  Chris_Layton_Final_Output.zip ( 29.56K )Number of downloads: 4
 
Go to the top of the page
 
projecttoday
post Apr 16 2020, 10:35 PM
Post#28


UtterAccess VIP
Posts: 12,166
Joined: 10-February 04
From: South Charleston, WV


I took a look at this and the one before. What am I supposed to be looking at?

--------------------
Robert Crouser
Go to the top of the page
 
MadPiet
post Apr 16 2020, 10:48 PM
Post#29



Posts: 3,701
Joined: 27-February 09



That's the output from the PowerQuery. It [censored] in data from a folder full of files, and unpivots it. Then he can import that into a Access. I could have done it in Access, but I was never a huge fan.
Go to the top of the page
 
ChrisLayton9
post Apr 17 2020, 06:41 AM
Post#30



Posts: 11
Joined: 3-April 20



Hi MadPiet

Format looks spot on!

Attached are some more example reports which will land each day - all data is of course just test samples.

Chris
This post has been edited by ChrisLayton9: Apr 17 2020, 06:43 AM
Attached File(s)
Attached File  Sample_Reports.zip ( 29.15K )Number of downloads: 3
 
Go to the top of the page
 
projecttoday
post Apr 17 2020, 06:48 AM
Post#31


UtterAccess VIP
Posts: 12,166
Joined: 10-February 04
From: South Charleston, WV


Does this replace Tableau? When finished, what does the user have to do?

--------------------
Robert Crouser
Go to the top of the page
 
ChrisLayton9
post Apr 17 2020, 07:35 AM
Post#32



Posts: 11
Joined: 3-April 20



No, Tableau will use the data in this format to drive some interactive dashboards.

Users won't have access to see the data - they will only have the front end dashboard which will be built on top of this data. By having the data in this format it means in Tableau charts and tables displayed so that when users click onto a specific area of the chart i.e. one type of specific error it will automatically filter a table on the same page to just show the records with errors and who made them.

Chris
This post has been edited by ChrisLayton9: Apr 17 2020, 07:35 AM
Go to the top of the page
 
projecttoday
post Apr 17 2020, 07:41 AM
Post#33


UtterAccess VIP
Posts: 12,166
Joined: 10-February 04
From: South Charleston, WV


So is adding Excel to the mix really necessary?

--------------------
Robert Crouser
Go to the top of the page
 
ChrisLayton9
post Apr 17 2020, 09:04 AM
Post#34



Posts: 11
Joined: 3-April 20



I think Power Query will enable all of the daily reports which drop to be merged into one sheet - this sheet will then become the source for the Tableau dashboard and is therefore a necessary step/tool and will remove the original Access database from the solution.

I know Tableau has its own prep tool - but its functionality is limited and I don't believe it would be able to do the 'normalising' which MadPiet has demo'd in Power Query.

Chris
Go to the top of the page
 
MadPiet
post Apr 17 2020, 09:15 AM
Post#35



Posts: 3,701
Joined: 27-February 09



projecttoday,

I was using it to do a gnarly unpivot. PowerQuery isn't available in Access. You can't even use CROSS APPLY … that's SQL Server... You can do it in VBA, but that mess is incredibly brittle. My thinking was that if I could automate the transformations, I could then import the files processed in Excel into a final database and they'd be clean already. If you want to do the text manipulation in Access/VBA then more power to ya.

Pieter
Go to the top of the page
 
MadPiet
post Apr 17 2020, 11:53 AM
Post#36



Posts: 3,701
Joined: 27-February 09



What are "all the reports"? I'm just trying to get my head around what this whole thing is supposed to look like.
Do you collect and summarize different things for the different sources? Please explain the end goal of the data. What kinds of reports are you going to do in Tableau?
Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    24th May 2020 - 06:28 PM