Organizing workbook

[schaelc]

I receive a published workbook about once a quarter. It contains several worksheets of different data and some worksheets contain multiple tables of data. I want to quickly and easily consolidate the whole workbook of data into one data table so I can generate a pivot report and the data can easily be utilized in custom formats to feed other models. Is a recorded macro the best? I would then rely on constant format of each workbook? My VBA knowledge is limited. Can power query do it better?

thanks

 

[sandy666]

Can power query do it better?

I don't know it will be better but for sure it can do that (depends on source data)

 

[schaelc]

I don't know it will be better but for sure it can do that (depends on source data)

Thanks- do you know a free resource that can show me how to transform the sheets that have multiple tables? Right now it just runs down the rows with null rows between new tables.

 

[sandy666]

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com

 

[schaelc]

Thanks again- I’ve been reading through the documents but I can’t find a solution. Append tables will work when I upload the workbook and it creates each tab as a table, but each sheet has multiple different tables that I need to append first. How do I fix each sheets multiple tables that power query thinks is one big table?

 

[sandy666]

There is no golden advice for your case. This is a "basic" documentation so you'll need get idea what you want to do, then get idea how to do it then do it. Power Query is not a Click&Go, you'll need to learn more.
You want solution but you even didn't show structure, relationships and type of your source data and expected result. Sorry but I forgot my Crystal Ball and cannot read in your mind
btw. pictures are useless.

 

[schaelc]

Is there a way i can upload a sample workbook of what im working with?

 

[sandy666]

sure, upload your excel file to the any service like onedrive, googledrive, googlebox or any similar, share this file then post link to this file here
remeber that it must be shared without any login, email address or something like that, just for download