Forum Discussion
Advocate ICombine Binaries with Different Header Names
I am trying to use a folder with CSV files as a data source. Occassionally, one of the files will come in with a different a slightly different header name than the other files (i.e. "DAY_C" vs. "#DAY_C"). Because the Sample Binary has "#DAY_C" as the header name, applying query changes results in an error because the "#DAY_C" column cannot be found in the file with the "DAY_C" header. These files are too large for me to open outside of Power BI to manually make the change to the header. Is there anything in Power BI that I can do to prevent this error?
Thank you!
Let me make sure I understand. Your CSVs sometimes have slightly different headers, so when you combine binaries you get an error because the headers don't match?
If that is the case, you will need to create a function that "cleans" the headers for each CSV before combining binaries. The pseudo code would be as follows:
- Get a list of the column names
- Check for leading characters (i.e. "#") and replace with nothing (i.e. ""). Depending on the number of permutations and the actual header you want to keep, perhaps you can compare the length of the string and extract a number of characters.
- Use this corrected list to replace the column names of the CSV
You would invoke this function via the Add Column > Invoke Custom Function command. Note, you will likely need to merge the file name and file path columns (from the folder view) in order to feed in a filepath to the function. After the function runs, it should return a table and you just expand the table, similar to combine binaries, that automatically appends all the data. Only now, there won't be column header problems.
1 Reply
- dkay84_PowerBI
Microsoft Employee
Let me make sure I understand. Your CSVs sometimes have slightly different headers, so when you combine binaries you get an error because the headers don't match?
If that is the case, you will need to create a function that "cleans" the headers for each CSV before combining binaries. The pseudo code would be as follows:
- Get a list of the column names
- Check for leading characters (i.e. "#") and replace with nothing (i.e. ""). Depending on the number of permutations and the actual header you want to keep, perhaps you can compare the length of the string and extract a number of characters.
- Use this corrected list to replace the column names of the CSV
You would invoke this function via the Add Column > Invoke Custom Function command. Note, you will likely need to merge the file name and file path columns (from the folder view) in order to feed in a filepath to the function. After the function runs, it should return a table and you just expand the table, similar to combine binaries, that automatically appends all the data. Only now, there won't be column header problems.