Forum Discussion
How to change the Excel file from report type to tabular type?
Hi,
I will receive three files once a month, the format of them as below:
3 files, once a month
I want to put them into a folder and use PBI to get the data, however, it's not a tabular type, does it need a manual change to a tabular type every time or any method to complete it fast and efficiently?
Many thanks and regards
Ryan
Hi RyanLau
Do you want to import data from three files into a single big table? If so, you can try Folder connector. Power Query Folder connector - Power Query Then combine files. Combine files overview - Power Query
When combining file, it will let you select a file as Sample file. After combining, you will find the queries are in the following structure. Select Transform Sample file, you will see the data in the sample file you select earlier. We need to transform data in this query, then the changes will be applied to all other combined files.
In this Transform Sample File query, we need to keep only the highlighted rows in below image and remove other rows.
So first click the drop-down arrow next to Column2 and select Remove Empty. This will remove rows that has empty values in this column. Then promote first row as headers via Home > Use First Rows as Headers. Then remove the top 1 row via Home > Remove Rows > Remove Top Rows.
You will have a table similar to below. This is what we want.
Then select Query1. This is the query that has combined result. If you see error in this query, it may be caused by a "Changed Type" step. You can delete this step from "Applied Steps" pane. This should solve it. When you are sure that all data is combined correctly, you can change column data types manually.
Next month when you have new files, you just need to add files into the same folder, then click refresh in Power BI Desktop to load data from new files.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
2 Replies
v-jingzhangCommunity Support
Hi RyanLau
Do you want to import data from three files into a single big table? If so, you can try Folder connector. Power Query Folder connector - Power Query Then combine files. Combine files overview - Power Query
When combining file, it will let you select a file as Sample file. After combining, you will find the queries are in the following structure. Select Transform Sample file, you will see the data in the sample file you select earlier. We need to transform data in this query, then the changes will be applied to all other combined files.
In this Transform Sample File query, we need to keep only the highlighted rows in below image and remove other rows.
So first click the drop-down arrow next to Column2 and select Remove Empty. This will remove rows that has empty values in this column. Then promote first row as headers via Home > Use First Rows as Headers. Then remove the top 1 row via Home > Remove Rows > Remove Top Rows.
You will have a table similar to below. This is what we want.
Then select Query1. This is the query that has combined result. If you see error in this query, it may be caused by a "Changed Type" step. You can delete this step from "Applied Steps" pane. This should solve it. When you are sure that all data is combined correctly, you can change column data types manually.
Next month when you have new files, you just need to add files into the same folder, then click refresh in Power BI Desktop to load data from new files.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.Hi Jing,
It's useful and informative, many thanks!
Regards
Ryan
