Forum Discussion

dolfinco2's avatar
dolfinco2
New Member
8 years ago

Bulk multi data export to Excel

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 whi...
v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

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's avatar
dolfinco2
New Member
8 years ago

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's avatar
    dolfinco2
    New Member
    8 years ago

    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's avatar
      danextian
      Super User
      8 years ago

      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's avatar
        dolfinco2
        New Member
        8 years ago

        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 ??