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

csv Folder-Source with slightly different columns

Hello everybody,


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

I have have a folder-source, where periodically new csv are loaded. Also it is not only two tables, is a constantly growing folder.


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

 

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.

Thank you!

1 ACCEPTED SOLUTION
dk_dk
Super User
Super User

Hi @NewPBIe ,

If you know all the possible column names that can occur, you can do the following:

 

Create a csv file in the same folder with all the other files (lets call it "csv_master") that contains all the possible column names, with 0 values.

When you connect to your folder, at the merge files step, make sure you select csv_master as the sample file:

dk_dk_0-1698138143878.png


and that should be it. The result should look something like this:

dk_dk_1-1698141474951.png

You can filter out csv_master from the Source.Name column, replace the nulls with blank or zeros as needed and you should be good to go.

If later on more columns are added to one of the csvs, you should be able to just come back to the master and add the extra columns.

Hope this works, let me know if you have any questions.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

3 REPLIES 3
dk_dk
Super User
Super User

Hi @NewPBIe ,

If you know all the possible column names that can occur, you can do the following:

 

Create a csv file in the same folder with all the other files (lets call it "csv_master") that contains all the possible column names, with 0 values.

When you connect to your folder, at the merge files step, make sure you select csv_master as the sample file:

dk_dk_0-1698138143878.png


and that should be it. The result should look something like this:

dk_dk_1-1698141474951.png

You can filter out csv_master from the Source.Name column, replace the nulls with blank or zeros as needed and you should be good to go.

If later on more columns are added to one of the csvs, you should be able to just come back to the master and add the extra columns.

Hope this works, let me know if you have any questions.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





@dk_dk Thank you. It has some manual work in it but it is still a good workaround if nothing else helps. 

 

Depending on the system that creates the csv files for you, you might be able to build some automation that updates the master csv file for you every time additional columns are introduced in your data ( I am thinking, Power Automate flow) but it is hard to say how without knowing more about the process that populates the folder. 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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