Bulk multi data export to Excel

[dolfinco2]

Hi there.  Brand new to BI and im looking to extract a lot of statistical data from horse racing tables that all have static data fields that suit my project.

Im "getting the data" for 7 races which gives me the following view.




The tick boxes are not ticked when all of the pages open so i need to start by expanding all, tick all 9 filters in the first race to create a file on the left then click outside, then repeat this for each race to create 7 separate files which i then need to export and save.

It ends up like this.

And the file pulls through like this.



Im looking to find out if there is a way that i dont need to individually create each sheet and somehow do a mass one, in the same format as the last file with all the races.  Or even if there is a quicker way to set it up that as each race gets entered in the right, the information is sucked out and put in format without having to go down and tick each box then untick them.

Hoping you can help.

[v-yulgu-msft]

Hi @dolfinco2,

You can combine all 7 racing tables into a single one via "Append Queries" as they share exactly same table structure and data columns.

Alternatively, you can place all these source files into a folder, then, get data from Folder rather than separate files from desktop. For more details, please see: Combining Excel Files hosted on a SharePoint folder with Power BI. This blog provides the solution to get sharepoint folder, in your scenario, you just need to choose the "Folder" option.

Regards,

Yuliana Gu

[dolfinco2]

Thanks for this and i have managed to apend the query but im struggling to see how i then extract that to Excel or CSV file.

Thanks in advance.

[dolfinco2]

Ok, i have now worked out how to extract the data which is good, however, it has combined all the data in 1 list and i really need to keep the format separated so each race is still in a format recognisable to that race with the split between each race.

[danextian]

Hi @dolfinco2,

A more elegant solution would be to connect to a folder that contains your CSV files and use the filenames as your identifier. Just Imagine having 100 CSVs, you wouldn't want to append them one by one.

But since you've already appended your queries, add a custom column to each query. You can name it as Identifier and in the formula field of the Add Column dialogue box, enter a text to identify your query with enclosed in quotation marks say "Query 1". This will add a column which value for each row is "Query 1". 

[dolfinco2]

Thanks for this and i like it as it gives me an additional functionality of adding date, race and class which is soemthing i have to to externally at present.

However, when i add these fields in on the front page it does this:

Then when i go to edit the query, the additional new columns dont pull through ??

[danextian]

What I meant was add a column in the query editor and not create a calculated column in DAX.

Please download a sample PBIX from the link below.

https://drive.google.com/open?id=17ILzYtoI7ORtCyAAMPXhuR2uwucZV-RM

Open the file, then click Edi Queries. You will see in the file that I have added a custom column which is used as as an identifer.