Forum Discussion
How can I create table from registration table
Hello,
I have a table which contains registration data which then want to be able to convert it into another table of different format automatically or with minimal steps. For example, I have the below table (where 1 represents the person attending on that particular day) :
And I want to get to another table, like the below from this data (the order doesn't matter and I don't have to have the name necessarily) :
Does anyone have any suggestions on the best way or ways to be able to quickly achieve this?
Samirv PowerQuery would be the easiest. collect your data in a structured table, query it and unpivot the date columns and then create the three column table as you described. Attached an example.
Append some new data to the table in Sheet1. Table expands automatically.
Go to the Table1 tab, right-click anywhere inside the table and select refresh, or select "Refresh all" in the Data ribbon.
If you re new to PQ, this is a good place to start (though there are many sites providing similar tutorials).
6 Replies
Samirv PowerQuery would be the easiest. collect your data in a structured table, query it and unpivot the date columns and then create the three column table as you described. Attached an example.
Append some new data to the table in Sheet1. Table expands automatically.
Go to the Table1 tab, right-click anywhere inside the table and select refresh, or select "Refresh all" in the Data ribbon.
If you re new to PQ, this is a good place to start (though there are many sites providing similar tutorials).
Riny_van_Eekelen This response was perfect. Solves my issue exactly and learnt something new too! Thank you so much for your help.
Samirv Glad I could help!
Riny_van_Eekelen Thank you for this! I am going through this to learn a bit about powerquery to see if I can use this!
With your permission, if I can recommend. It can help us all if you upload an Excel file (without sensitive data), no picture.
Even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases.
You could get a precise solution much faster with a file (w/out sensitive data).
This would also be a blessing for all of us, as we can understand the problem much better, a win-win situation for everyone.
Some users (like me 🙂 who want to help do not understand English well and the translation does not always give what you want to say, only what it can translate in an emotionally neutral way.
This is also one of the important reasons to always add a file with the description of your operating system and Excel version.
Thank you for your understanding and patience
Nikolino
I know I don't know anything (Socrates)
NikolinoDE Thanks for your reply - I have attached a file with the data now - basically I want to know how can I get from the data in yellow to the data in green! Thank you,
