Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
09
Helper I
Helper I

CSV_Files import with power query

Hello,

I'm wondering if there is a way of directly import excel files from Microsoft Exchange with the format of csv files.

Thank you for your help.

1 ACCEPTED SOLUTION

Power Query can't convert anything through its steps. PQ can only import the Excel files, combine them. Once you have final table after combination, you can do Close and Apply. Once the results are committed to Power BI,

1. Right click on your table - Copy table - Paste into Excel - Once data is into Excel, save as csv. (Larger the dataset, it will take more time and also you can't paste more than 1million rows of Excel sheet limit)

2. OR You can prepare any visual, publish it to Power BI service and Power BI service offers Export to csv. 

Export the data that was used to create a visualization - https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-export-data?tabs=dashboard

3. OR DAX Studio which is free and open source offers the facility to export table data as csv

https://daxstudio.org/documentation/features/export-data/ 

 

Note - If you are invoking your PQ from Excel and final result is having less than 1 million rows limit of Excel, then you can get the result into Excel itself and then save as csv from Excel. If you are having more than 1 million rows, say 5 million rows.

- Insert an Index

- Create 5 reference queries

- In first reference query, apply the filter from 1 to 1 million, in second reference query from 100001 to 2 million and so on for all 5 reference queries.

- Close and Load To and choose as Connection only (Don't choose Close and Load otherwise all 5 queries and original query will try to load into same sheet). Now all queries will be loaded as Connection only.

- Once you are back into Excel, right click on a reference query and save to a table. Do it for all 5 reference queries into different sheets.

- Now copy the data into 5 different Excel workbooks.

- Save them as csv

- Use a tool such as Notepad++ to merge all these 5 csv files into. (Don't forget to remove headers from remaining 4 files)

View solution in original post

3 REPLIES 3
Vijay_A_Verma
Super User
Super User

Yes, you can use Microsoft Exchange connector. The complete method to import csv/excel attachments from MS Exchange is nicely covered here

Import Email Attachments Directly Into a Power BI Report using Power Query 

Thank you for your response.

I need to know in case if all the attachements in the mail box are in EXCEL format, could we convert it to a csv format?

Power Query can't convert anything through its steps. PQ can only import the Excel files, combine them. Once you have final table after combination, you can do Close and Apply. Once the results are committed to Power BI,

1. Right click on your table - Copy table - Paste into Excel - Once data is into Excel, save as csv. (Larger the dataset, it will take more time and also you can't paste more than 1million rows of Excel sheet limit)

2. OR You can prepare any visual, publish it to Power BI service and Power BI service offers Export to csv. 

Export the data that was used to create a visualization - https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-export-data?tabs=dashboard

3. OR DAX Studio which is free and open source offers the facility to export table data as csv

https://daxstudio.org/documentation/features/export-data/ 

 

Note - If you are invoking your PQ from Excel and final result is having less than 1 million rows limit of Excel, then you can get the result into Excel itself and then save as csv from Excel. If you are having more than 1 million rows, say 5 million rows.

- Insert an Index

- Create 5 reference queries

- In first reference query, apply the filter from 1 to 1 million, in second reference query from 100001 to 2 million and so on for all 5 reference queries.

- Close and Load To and choose as Connection only (Don't choose Close and Load otherwise all 5 queries and original query will try to load into same sheet). Now all queries will be loaded as Connection only.

- Once you are back into Excel, right click on a reference query and save to a table. Do it for all 5 reference queries into different sheets.

- Now copy the data into 5 different Excel workbooks.

- Save them as csv

- Use a tool such as Notepad++ to merge all these 5 csv files into. (Don't forget to remove headers from remaining 4 files)

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors