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
mmace1
Impactful Individual
Impactful Individual

Combining Excel files - columns don't line up. Headers are consistent however - way to automate?

Hi, 

 

I have a folder containing Excel files that need to be combined.  Periodically though, it will get a new Excel file.  This file may have new columns in it - and if it does, they may be placed in the middle of the existing ones.  Not so good for combining the files automatically.  The new columns will have new header names, but the old ones will be consistent. eg. 

 

 

Say the first file's headers are like this: 

Puppies    Kittens    Rainbows

Then the 2nd file adds a new column, but where will it go - nobody knows!

 

Puppies   Sparkles   Kittens     Rainbows

Is there a way to get Power BI to automatically combine all the Puppies, Kittens, Rainbow data + a column for Sparkles?  If Sparkles / other new columns were to always appearall the way to the right, perfect, but they're not. 

 

Every time a new file comes in (not that often), I could append the new file manually.  Since it's not that often - that's viable.  But, just curious if there's a way to automate this, despite the columns not lining up. 

 

Thanks, 

 

 

1 ACCEPTED SOLUTION

Hi @mmace1,

 

Please check out the demo here. First of all, you need to install R software.

Why not excel workbooks? Because some packages aren't supported in the Power BI.

# 'dataset' holds the input data for this script
library(data.table)

dirs <- dataset$"Folder Path"  # dataset is the Source from the last step
file_names <- dataset$Name
paths <- paste(dirs, file_names, sep = "")   # get all the file paths.

result <- data.frame()  # create a empty list to initialize
for (path in paths){   # read all the csv files in the paths
    temp <- read.csv(path)   
    result <- rbindlist(list(result, temp), use.names = TRUE, fill = TRUE)  #bind two lists and keep the new columns.
}

Combining_Excel_files_columns_don_t_line_up_Headers_are_consistent_however_way_to_automate

 

Best Regards,

Dale

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

View solution in original post

3 REPLIES 3
v-jiascu-msft
Microsoft Employee
Microsoft Employee

Hi @mmace1,

 

I think the most possible solution could be using R script. 

1. If it's convenient for you to use a "csv" type of files instead?

2. Will you refresh the report in the Power BI Service?

 

Best Regards,

Dale

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

@v-jiascu-msft

 

It's not really convenient to use .CSV, no.  And in this case I'm just going to manually append each file as it comes in (just 1x a month)

 

But for the sake of argument / I am just curious - sure,  is .CSV better for R Script?

 

In a general sense, how would one go about using R Script for this?

 

 

Hi @mmace1,

 

Please check out the demo here. First of all, you need to install R software.

Why not excel workbooks? Because some packages aren't supported in the Power BI.

# 'dataset' holds the input data for this script
library(data.table)

dirs <- dataset$"Folder Path"  # dataset is the Source from the last step
file_names <- dataset$Name
paths <- paste(dirs, file_names, sep = "")   # get all the file paths.

result <- data.frame()  # create a empty list to initialize
for (path in paths){   # read all the csv files in the paths
    temp <- read.csv(path)   
    result <- rbindlist(list(result, temp), use.names = TRUE, fill = TRUE)  #bind two lists and keep the new columns.
}

Combining_Excel_files_columns_don_t_line_up_Headers_are_consistent_however_way_to_automate

 

Best Regards,

Dale

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

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.