Combine various formatted files from single folder in power bi

[Asd123]

Hi,

Consider in single folder there are multiple .csv and .tsv files having different column names, column order.

Is there any way to combine this files other than doing manual transformations?

Thanks in advance.

[Anonymous]

Hi @Asd123 ,

Yes, there is a way to combine multiple formatted files from a single folder in Power BI without manual transformations. You can use the Combine Files feature in Power Query Editor to combine all the files in a folder with the same schema into a single logical table 12. Here are the steps to follow:

1.In Power BI Desktop, select Get Data and choose Folder.
2.Enter the folder path and select OK.
3.Choose Transform Data to open the Power Query Editor.
4.In the Power Query Editor, select the Content column and choose Combine Files from the Combine group on the Home tab.
5.In the Combine Files dialog box, select the file format and delimiter that matches your data.
Select OK to combine all files in the folder into a single table.

Combine files (binaries) in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[Asd123]

Hi,

Consider no column names are given in file1 (it takes default column names column1,column2 etc in transform data) and columns in file2 are a,b,c.

If I do it in way you suggested, it combines file1 and file2 but file1 with nulls and file2 with data.

Need to map columns first from file1 to file2 and do combine. Mapping is for placing some correct data into correct column. Is there any way to achieve this?

Thanks in advance.

[ChiragGarg2512]

@Asd123 , In power query[transform data], in combine group of home tab there is the option to merge queries can be used to join two tables[Joins as in database joins(left, right, full, etc.)].

[Asd123]

Hi,

Joining is not applicable for me.