
Power Query

August 3, 2020

Hello,
I am creating a workbook to distribute to a number of users, using Power Query to pull data from their own sources. The resultant table, with about 20 fields, should have different headers depending on the type of user and I'm looking for the simplest, most elegant, solution.
Example: Let us say that the users represent a Town or a City. I therefore want the headers to say either "Town Name, Town Population" or "City Name, City Population" etc. It's not always just a matter of replacing "Town" with "City", though.
Current position: I currently use VBA to test the content of a particular cell and then run a subroutine containing
With Sheet1.ListObjects("tblData")
.HeaderRowRange(1) = "Town Name"
.HeaderRowRange(2) = "Town Population"
End With
And so on.
Is there a better solution? I was thinking of having the values set out in a separate table or list in the workbook and then using VBA to transfer those values to the row headers.
Many thanks in advance.
Pieter

VIP

Trusted Members

December 7, 2016



November 8, 2013

Hi Peter,
You can have a separate table with as many columns you need, and write the headers in a single operation:
ActiveSheet.ListObjects("Table2").HeaderRowRange.Value = Application.Transpose(ActiveSheet.ListObjects("HeadersTable").ListColumns("Column1").DataBodyRange.Value)
I assume the number of rows in the headers table is equal to the number of columns in the destination table.

Answers Post
1 Guest(s)
