Please watch the video in 1080p because it is recorded in High Resolution.
Please note the script of the presentation is below so you can read along with the demonstration if you like.
Thank You for dropping by to read our latest, How To, Blog Post.
In this video, we give you a full demonstration, of how to send data from any Meta5 query in to an excel spreadsheet, and how to create a table in power pivot based on that query.
Please be aware, that you can refresh and update the power pivot tables, and all charts, based on these tables using Meta5.
This means it is possible, to run capsules every night, and to send out the updated Excel reports to the required business users every day.
The business users receive spreadsheets that contain just the data that is relevant to them.
They do not need any open connection back to any database to view their spreadsheets.
So, as ever, on with the demo.
Here we are, on the Meta5 evaluation workstation desktop.
I have placed a capsule in the middle of the desktop.
We will open the capsule.
We have a query icon, for the sales fact table, from the World Wide Importers.
We will get the other icons we need for this capsule, from the new icons file drawer.
We will need some spreadsheets.
We will need an excel workbook as the target.
We will need the compress icon to remove the header row.
We will arrange the icons in the capsule, so that the flow of data is easy to understand.
We will open the excel workbook.
We will create a region from A one to tee one.
We will call that region Data Name H to mean data name headings.
Again, it is clear you can use your own naming standards.
We will create a region on line two called Data Name D, for the data name data region.
We will update the name of the worksheet, to Data Name S, to comply with our naming standards.
As we mentioned, you can use your own naming standards.
We will show you that the two regions are named, by referencing them in the formula bar.
We will close the spreadsheet.
We will open up the query icon.
In the general qualifier, we will add the condition zero equal one.
We will run the query, to show you, that no rows are returned, only the headers.
We will connect the query icon to a spreadsheet, that we will call the Headers spreadsheet.
We will run the query to the headers spreadsheet.
We will open the headers spreadsheet.
Now you can see the headers in the spreadsheet.
Of course, we can edit these headings if we want to.
We will delete the link from the query, to the headers spreadsheet.
We will create an arrow between the headers spreadsheet, and the excel workbook.
We will option the arrow.
We will copy the data from the Headers spreadsheet, to Data Name H, in the target excel workbook.
We will run the headers into the workbook along the arrow.
We will open up the excel workbook, and you can see the headings have flowed through to the workbook.
We will open the query.
We will remove the general qualifier, of zero equals one.
We will edit the compress icon.
We will set it to delete one row, the headings row.
We will connect the icons, to tell Meta5 how to flow the data.
We will option the link between the final spreadsheet, and the excel workbook.
We will set the arrow, to send the data, to region name Data Name D.
We will delete the arrow between the Headings spreadsheet, and the Excel workbook.
This is because the headings only need to be set up once, when the spreadsheet is created.
Now we will run the capsule.
We will trim the video so you don’t have to wait.
We will open the excel workbook.
You can see that the data was delivered to the workbook.
We will select all the data in the workbook.
We will insert an excel table.
We will rename the table Data Name tee.
You can see that the two regions, and one table, are available from the formula bar.
We will add the table to the power pivot model.
You can see that the sixty thousand rows were loaded, into the power pivot model.
Now we will refresh the model, just so you can see, that it reads in the sixty thousand rows again quickly.
We will save and close the spreadsheet.
We will log off the Meta5 desktop.
And there you have it.
What you have seen today, is how to send the data from a meta5 query icon, to a power pivot table inside excel.
This includes, how to send the column headings into the excel workbook, just once, to set up the column headings.
You can edit the column headings in the meta5 spreadsheet, or the excel spreadsheet, before creating the power pivot table.
Once you create the power pivot table, you should not change the column names again, because of the objects that will be created on top of them.
If you do not follow this practice, it is possible for the column names to change, in the query tool.
This is because these column names are maintained in a dictionary.
It is best practice to always create a headings worksheet, and send the headings in to each worksheet in the spreadsheet, which will contain data, only once.
And with that?
I would like to say thank you very much for watching our demonstration today.
I really appreciate your time and attention.
We hope you found this video interesting and informative.
We would love to hear your comments so please feel free to use the comments section below.
If you would like to get in touch with Mihai, his details are on our contact page.
I wish you a great day.
Thank you.
Sofi.
Your BIDA AI Assistant.