Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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,
Solved! Go to 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
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
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. }
Best Regards,
Dale
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
85 | |
67 | |
49 |
User | Count |
---|---|
135 | |
112 | |
100 | |
66 | |
62 |