Transpose

Hello,

Here is a subject posted on this forum that interests me


I would like to know how to indicate the column headers in the file at p45cal (#6): Product, Date and Name
On the other hand, even the p45cal solution is very good and I almost understand the solution but it gets a bit complicated with the steps in red (see screenshot),
For example, the "RecVal" step uses another "ExpandedTable1b" step below.

Do you have please a slightly simpler solution?



View attachment ExcelGuru10745sample.xlsx

Dico said:

I would like to know how to indicate the column headers in the file at p45cal (#6): Product, Date and Name

Click to expand...

Neither Excel's Tables, nor Power Query will let you have duplicate column headers in the same table - they must all be unique.
In the attached I've added a new query, Table2 where I've tried to use the user interface as much as possible, and where there's been some Advanced Editor editing I've kept it in simple steps. It uses a custom function, but again that's been created using only user-interface steps (before converting it into a function).
It's not got as many checks and balances in but I'm assuming you're getting your head round Power Query (as I am!) and this is more a 'how might I do x?' exercise.
What I've done at the end, is inserted a first row of potential column headers. If you want, you can try and promote that row to headers, but you won't like the result! Otherwise all you can do is, on the worksheet, convert the table to a plain range (removing the query from the table) and then remove the headers row leaving the first row as headers, but it's no longer a proper table. If you try to reconvert it to a Table it will mangle the headers once again.

Hello p45cal,

Thank you very much for this solution and the relevant remarks.
You are right to point out that duplicate titles are not possible in Excel tables and queries. Sometimes I don't even think about the basics of Excel anymore

Otherwise, I'll look at the file in detail, I think I won't have too much trouble understanding the code. And I prefer this 2nd solution:smile:
Concerning the titles "Column1", "Column2, Column3"...., the easiest way is to uncheck "Header Row" in the Design tab of the table to hide the titles.

Indeed, I actually take most of the topics of the site to practice and learn more.
I will have learned a little more about Power query tonight.



Thanks again

Have a nice day

Dico said:

Concerning the titles "Column1", "Column2, Column3"...., the easiest way is to uncheck "Header Row" in the Design tab of the table to hide the titles.

Click to expand...

True enough… I'd forgotten about that.

p45cal said:

True enough… I'd forgotten about that.

Click to expand...

Like me, for forgetting the duplicate titles :doh:

Sincerely

Hi p45cal,
I like this solution,
I simulated the function by transforming it into a query as on the image
The code and the function are very good
Thanks again