Power Query Table Manipulation

[Steveapa]

Hi All.

I have a query to an odbc to get my sales forecast by Year 1, Year 2, Year 3... and Start year. I want to convert the table to something more usable. Example below. Top is how I get the data, bottom is what I want I have done it in vba, but how to do it in Power query?

Capture.JPG

Thanks
Steve

[AliGW]

Please attach the workbook.

[Steveapa]

Hi AliGW.

Thanks for the help, but I don't see an attached workbook??

Thanks.

[AliGW]

I asked you to provide the workbook. See instructions in the yellow box at the top.

[Steveapa]

Sorry, I mis-read. I have attached a sample file. The real data comes from an odbc to a server. I have pulled in some of the data to a Tab "Source Data for Power Query" and changed that to the query source. I am looking to use power query to convert the data to the format of tab "Results I want Format"

Thanks for the help.
Steve

[Ron Coderre]

See if you can work with the attached file. I didn't have a lot of time to work on this so the queries are a little ragged.
Basically, I created a function that transforms one row from the source data into the structure you asked for.
Then I created a comprehensive query that processes each record from the source data, using the function, and performs the final transformations.

Is that something you can work with?

[Steveapa]

Sorry... but I am getting an compatibality error. YOu must have a newer version of excel than I do. Error is in your fnTransformRecord. An error occurred in the " query.expression.error: The name 'Splitter.SplitTextBGyCharacterTransition' wasn't recognized.

I have Microsoft Excel for Office 365 MSO (16.0.10730.20264) 32-bit

[Ron Coderre]

Not sure where you're finding "Splitter.SplitTextBGyCharacterTransition" in my code.
The function in my file contains "Splitter.SplitTextByCharacterTransition"

[AliGW]

@Steve - please update your forum profile to show your current version of Excel. Thanks.

[Steveapa]

Profile updated

[Steveapa]

I got it!!!! Here is what I did. I unpivoted all forecast Yr1, forecast Yr2, etc column. Then used the column name to pull out the 1 from Yr1, the 2 from Yr2... I then added this number to the Start year in a new column. Deleted the unpivoted column. Then I pivoted the new column which now has the actual year number.!!!!

How do I mark this as solved???

Thanks for your help anyway! Hope my solution helps someone else
Steve

[Steveapa]

Here is the example file posted with the solution.

Thanks again
Steve