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

[mmace1]

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, 

[v-jiascu-msft]

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

[mmace1]

@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?

[v-jiascu-msft · 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.
}

Best Regards,

Dale