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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
LoremIpsum
Regular Visitor

Import Data from Folder with Files That Have Additional Headings

I need to import data from 2 groups of files that have the same structure EXCEPT 1 group of files has additional columns that I need.

 

Group 1 has 4 relevant data columns. Group 2 has the same 4 relevant data columns but it also 5 additional columns I need to import. Both groups have multiple files.

 

One a single day, where there is 1 file from each group, I can import each file and merge the contents into 1 grand table. I figure, but haven't tested, that I can import from two different folders and then merge aftewards; however, I prefer not asking the users to separate out the files into a group 1 folder and a group 2 folder. The ideal solution would be dumping all the files from both groups into 1 folder, importing, and letting the program merge the contents.

 

Thank you.

6 REPLIES 6
v-qiuyu-msft
Community Support
Community Support

Hi @LoremIpsum,

 

You can place those two kind of files in one folder, then in Power BI desktop get data use the Folder data source, then use Combine Files follow this article: Combine binaries in Power BI Desktop

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-qiuyu-msft I'm not sure that article helps. It specifically says at the end, "With the new behavior of combine binaries, you can easily combine all binaries within a given folder as long as they have the same file type and structure (as in, the same columns)."

 

Half of my files have less columns than the other have, although the half with less have the same columns as the other group. It's just that the other group has additional columns that I also want to keep.

Hi @LoremIpsum,

 

Have tried that Combine Files in desktop? Based on my test, when you click the Combine Files in Query Editor, then select the Excel which has additional column as sample, it will combine additional column as well, if some Excel file doesn't have this additional column, it will display as null value. 

 

q8.PNGq9.PNG

 

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

If you combine files from a folder, you can choose which file should act as the example file.

You can try and choose one of the files that has all columns.

Possibly that will be enough to have all files imported correctly.

If not, you may need to specify that missing fields should be ignored.

 

Anyhow, you can give it a try. If you run into errors, please share these (well specified so we know what is going wrong in which step) so we can help finetuning your queries.

Specializing in Power Query Formula Language (M)

I had tried your tip before posting without success. Here is what I get back: [Expression.Error] The key didn't match any rows in the table.

 

Another problem I forsee is my first file is always the shorter version, and the filenames change every day so I can't select the longer version file.

If all column names are known, then you can adjust the Transform Sample query and use Table.SelectColumns(Name-of-previous-step,list-with-column-names,MissingField.UseNull).

 

If any of the columns is missing, then the column will be created with null values.

 

E.g., if your previous step is "Source"  and your required columns are Column1 and Column2:

 

SelectColumns = Table.SelectColumns(Source,{"Column1","Column2"),MissingField.UseNull)
Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

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! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.