Excel report displaying additional blank columns (paginated report)

[Marion]

Hi

I am using paginated reports to issue an Excel file that will be given to Accounts Payables for journal entry.

My problem is that values are diplayed in several merged columns in the Excel format:

Id like no blank columns between each item.

Thanks for your help

[starsiddarth]

Thank you... carnival rental

[v-jingzhang]

Hi @Marion 

One option is to split the merged columns and remove blank columns in the Excel file in advance. Then use this file to create your paginated report. 

Another option is to use Power Query to remove those blank columns. You can use Power BI Desktop to connect to this Excel file first. Then in Power Query Editor, remove all blank columns manually or dynamically with a custom function. Apply the data and publish the pbix file to Power BI Service to have a dataset. In Report Builder, connect to this dataset to create a paginated report. 

Dynamically Remove Empty Columns in Power Query 

DYNAMICALLY Remove All Empty Columns with some M MAGIC in Power BI 

Power BI Paginated Reports with Excel Source 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Marion]

Hi,

I don't think that this is the optimal solution. Let me give you more information about my problem:

- I have a PowerBI database that I publish on the PowerBI Service Platform in a Premium Workspace

- I built Paginated Reports based on this database thanks to PowerBI Builder. These paginated reports are also published in the Premium Workspace

- Once the database updated, I generate my paginated reports in Excel format through the PowerBI platform.

Then my question is, is there a way for paginated reports to not merge cells when exported to Excel ? The Excel export is not usable for account purposes, there is too much clean up to do. The turnaround I found is to export the paginated report in a csv. format and then run a macro to have a good layout. Not efficient.

[v-jingzhang · Accepted solution]

Hi @Marion 

Sorry I misunderstood it. I found some solutions to this problem of SSRS reports. Hope they are helpful. You would need to adjust the column header or text box widths and positions in the paginated report.

How to Eliminate Excel Column Merging in Exported SSRS Reports | PTR

How to Export to Excel Without Making a Mess - Red Three Consulting

How do I get rid of Excel column merging in export SSRS report? – QuickAdviser (quick-adviser.com)

Below the suggestions from the official document: Export a paginated report to Microsoft Excel (Report Builder). 

If it is important that reports exported to Excel worksheets can be sorted, then the following can help you reduce the number of merged cells in your Excel worksheets, which is the common cause for difficulties with Excel sort functionality.

• Not aligning items left and right is the most common cause of merged cells. Make sure the left and right edges of all report items line up with one another. Making items align and the same width will solve the problem in the majority of cases.

• Although you align all items precisely, you might find in some rare cases that some columns continue to be merged. This could be caused by internal unit conversion and rounding when the Excel worksheet is rendered. In the report definition language (RDL), you can specify position and size in different measurement units such as inches, pixels, centimeters, and points. Internally the Excel uses points. To minimize conversion and the potential inaccuracy of rounding when converting inches and centimeters to points, consider specifying all measurements in whole points for the most direct results. One inch is 72 points.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.