Center across selection in a power query output

[MathGeek]

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.


[IMG alt="thumbnail image 1 of blog post titled











Center across selection in a power query output












"]https://techcommunity.microsoft.com...8300A529F/image-size/medium?v=v2&px=400[/IMG]

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!

 

[jdellasala]

The only way to attain what you want is to insert a new column into the original Power Query table:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddedCustom = Table.AddColumn(Source, "Custom", each ""),
    ReorderedColumns = Table.ReorderColumns(AddedCustom,{"ColumnA", "Custom", "ColumnB"})
in
    ReorderedColumns

And then use formulas to create the table you want:

Book1
	A	B	C	D	E	F	G	H	I	J
1	ColumnA	ColumnB		ColumnA	Custom	ColumnB		ColumnA		ColumnB
2	1	2		1		2		1		2
3	3	4		3		4		3		4
Sheet1

Cell Formulas
Range	Formula
H1	H1	=pq_Table1[[#Headers],[ColumnA]]
H2:H3	H2	=pq_Table1[ColumnA]
J1	J1	=pq_Table1[[#Headers],[ColumnB]]
J2:J3	J2	=pq_Table1[ColumnB]
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
ExternalData_1	=Sheet1!$D$1:$F$3	H1

Note that the preview above does not show how the formula for ColumnA as well as the values below it are centered across selection. I'll add that Merge and Center is almost NEVER a good option. The formulas will behave mostly like an actual Table and expand as the source data expands.

 

[MathGeek]

Thanks for your reply. The problem with the formula solution is that it does not push down things below the table. This is why I was using power query. Basically I want to create a billing sheet. Below the table there is text, taxes, subtotal and total. This is link to a timesheet that I've created in another sheet. I don't mind about formula behavior as there's none in the table, that's why I would be fine with a range as long as it push down what's below. Does anybody knows why center accross selection works on normal table but not power query output?