I have Excel 2016 and a PC on Windows 11.
I want to have a power query table output where the data in the first column is centered over 2 columns, the original one and an empy one. I would also like for the first column label to be centered over those 2 columns. A minimal case is shown in the picture below.
Note that a solution that ouput a table or a range would be acceptable for me. However, the solution has to be dynamic, so no transformation needed every time the input table change.
Here's some ideas that I tried:
1 - Try to make power querry output a range instead of a table. The only thing that I was able to do was to output a table and than manually change it to a range, but that does not meet my requirements. Being able to do this would solve all of my problems.
2 - Merge and center - does not work on tables
3 - Center across selection - Strangely, this works on normal tables but not on power query output tables.
Also, this solution does not work to center the column label as Excel automatically filled the second column name with a default name (can't have a blank), hence center across doesn't work as the second column label would have to be empty.
4 - For the header case, I also tried to hide the output table header and construct an artifical header over the table by forbiding excel from automaticaly add rows or column when the user input value close to a table. That kinda worked but it's not robust against an external user that would click to reshow the header as it would makes it crash (tested). What would be robust would be to output a table from power query that has no header at all. Promoting the first row as a header does not work as it still shows the first row as a header in the output table.
The reason I want to achieve this is for formating reason as there needs to be 2 columns over column A above and below the table. I also need to use power query as I want the output table to have the same number of rows as the original table and there are other cells right below the table that need to shift down if I add a row to the input table (and this works well for me as it is).
Thank you for your help!