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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
NewPBIe
Helper II
Helper II

Combine a folder with excel and csv files on it with slightly different structures

Hello everybody, 

 

I need to dynamically adapt the list of columns from multiple files in excel and csv with slightly different columns. 

My output table should contain all the columns from every file and properly assign each value from every file in the same column, if the name among the different files is the same. 

 

This should be the anwer: https://blog.crossjoin.co.uk/2017/12/03/making-sure-all-columns-appear-when-you-combine-data-from-mu...

 

i.e. use Table.Combine instead of Table.ExpandTableColumn, but these functions are not interchangable, because they expect different arguments: 

 

Table.Combine(tables as list)

Table.ExpandTableColumn(table as table)

 

Is there a workaround for this?

 

Thank you!

3 REPLIES 3
lbendlin
Super User
Super User

Both approaches are valid, but there is a third, much more elegant one.   " table1 & table2 "   will give you the same functionality - mapping matching columns and adding non-matching columns.

 

You can achieve that also be Table.AddColumn from your list of files, and a custom function to ingest each file.

 

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

@lbendlin thank you, but as I said I have have a folder-source, where periodically new csv are loaded. 
Also it is not only two tables, is a constantly growing folder.

Here two csv samples and a pbi that shows, that only the structure of fileone is properly represented, while for filetwo the column E is missing completely

Don't mind the value "5" missing; the filetwo B-named column has a space after it, which explains why it hasn't been recognised. 

Unfortunately I have no means to share data outside my organisation. 

 

fileone: 

NewPBIe_1-1698046958805.png

filetwo:

NewPBIe_2-1698046975580.png

desired output: 

NewPBIe_3-1698046992139.png

actual output:

NewPBIe_4-1698047079828.png

second request: Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors