Help with Excel data saved using tidyverse "Not acceptable" by PowerBI

[ksingh19]

Hi,

I transformed the data and stored in excel sheet which I wanted to use for Power BI. However, when we store data in excel, some columns have truncated headers and values and thus, I used the following code to have clean columns or auto-adjust widths for column

setColWidths(wb, "tab_name", cols = 1:6, widths = "auto")
#using library(openxlsx)

Excel sheet looks fine. But Power BI doesn't accept this file. However, if I manually adjust the excel sheet by double-clicking on the column line, Power BI accepts it.

Is there anyway to avoid adjusting the column width so that Power BI can accept such file.

Thanks!

[startz]

It might work to open the file in Excel and then save it again. Admittedly, a kludge.

[ksingh19]

@ksingh19

if I manually adjust the excel sheet by double-clicking on the column line, Power BI accepts it.

Yes, as I mentioned earlier, if I manually adjust the excel sheet by double-clicking on the column line, Power BI accepts it. But if we have many datasets like this, and if we want to refresh the data script automatically, then this manual approach doesn't help.

If anyone can help with this problem, it will be much appreciated!

Thank you all!

[startz]

My suggestion was to open and save it without worrying about adjusting the column. Admittedly, that still requires some intervention.

The problem is likely that many programs are fussy about the exact specs of an Excel worksheet. Excel itself is less fussy.

[nirgrahamuk]

If this is about loading info from R to Power BI, I would suggest that excel as a transmission format between the two is inferior to simpler data format, i.e. CSV
Could you switch to .csv?

[ksingh19]

Thanks @startz !
Understood now and also tried it. It works also. Thanks for sharing this tip. I wasn't aware. Definitely better option that adjusting the column each time.
I still hope there is a way we are not aware of! But thank you so much!

[ksingh19]

Thanks @nirgrahamuk !
Yes, csv file has no issues. The only issue is that we can't add multiple sheets within a file like we do in excel which is helpful in many cases. Unless there is a way to add and save such multiple sheets within csv file, which I am not aware of. Then, definitely that might be the best way to approach at least in my case.

Thanks again!

[nirgrahamuk]

I believe power bi can unzip gzip files, so multiple csvs can live together in a gzip

[startz]

You can probably write an R-script that opens a file in Excel and then saves it to completely automate the process. I will have to admit though that I have no idea how to do it.

[acullum]

You can probably write an R-script that opens a file in Excel and then saves it to completely automate the process. I will have to admit though that I have no idea how to do it.

You could definitely write an Excel/VB macro that would do this.

[ksingh19]

Thanks @startz !
This is great idea! I am also not sure how to do that. But I will research on that. Hopefully, I can find something and share with you all.

[ksingh19]

Thanks @acullum!
If I can't find a way to write R-script for that, I might need to come back to VBA. I never really spent time in learning VBA. So, I might need to research on that too as an alternative option.
Thanks!

[ksingh19]

Thansk @nirgrahamuk!

[ksingh19]

Thank you all @nirgrahamuk , @startz and @acullum!

I finally ended up adding one more line of code using openXL(".....xlsx") from openxlsx package to open the excel file. It instantly opens up the file. I do have to close it manually, but this is so far the easiest option I could find. I wish there was a close function similar to that, but I couldn't find that option. If anyone hear about something like that, please share.

I also researched on VBA and seems to be very smooth as well. But I preferred openXL() to be able to stay with one script. If we do have a close function out there, then the script will be complete and definitely would be a great option!

Thank you all!