Hello, I have a spreadsheet with rates per lb that I need to transpose into a different format so I can upload into a rating system. I have attached an excel spreadsheet with the original data on the first tab and the desired result on the second tab. I am trying to figure out an easy way to this as the main file has about 1000 rows that need to be transposed. Hope someone can help.
With Power Query/Get and Transform which is on the Data Tab. Here is the Mcode resulting from that transformation
Review PQ
Please Login or Register to view this content.
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps
M-code basics:
- "let" is the start of a query
- "in" is the closing of a query
- each transformation step sits in between those 2 lines
- each step line is ended by a comma, except the last one
- "Source" is always the first step (Source Data)
- After "in" you have the last step referenced
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
It absolutely worked. I added the additional data to the file that you sent and refreshed the Query and it populated all the data I needed. Thank you so much. You saved me a ton of time. I do have a question though, where can I see the Mcode in case I wanted to manipulate it by adding more columns to the Source file?
It absolutely worked. I added the additional data to the file that you sent and refreshed the Query and it populated all the data I needed. Thank you so much. You saved me a ton of time. I do have a question though, where can I see the Mcode in case I wanted to manipulate it by adding more columns to the Source file?
Just in case you want a different option, here is a code process:
Please Login or Register to view this content.
With Power Query open in the editor, there are two places you can see the Mcode.
1. On Home, click on the Advanced Editor
2. On View Tab, click on Advanced Editor
Thank you. This does provide me with another option as I have to do a bunch of these every month. I appreciate you taking the time to respond!Originally Posted by maniacb
Just in case you want a different option, here is a code process:
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks