Log In
Register
Home
June 17, 2020
Offline
I need to transform data as in the attached file. Is it doable in MS Excel? If so how? What is the best tool to do it? MS Excel, MS Power Query, MS SQL Server Integration Services Stored Procedure? Thank you for your support. Kind regards, S.
July 16, 2010
Offline
Hi Lukasz,
No file attached. Be sure to click the 'start upload' button after selecting your file.
That said, Power Query is most likely the tool for the job. See this video on unpivoting data with Power Query. Come back with your example file if you're still stuck after watching the video.
Mynda
June 17, 2020
Offline
Thank you for the reply. I uploaded the file. Will watch the video.
June 17, 2020
Offline
It looks like I have to do pivoting almost exactly opposite to what you do in the Example 1. form the video on unpivoting data with Power Query. L.
June 17, 2020
Offline
I have one more issue.
I have two data warehouse tables: SALES, 140 variables, 10 years, 12+ mln rows and ORDERS, 60 variables, 10 years, 12mln+ rows. Is the MS Power Query the right tool to create Excel table with ca. 5000 rows of data including product code and ca. 50 columns (variables) of monthly sales data (quantity)? Is the MS Power Query code (what code?) executed on database side (SQL) or Excel side? If it is executed on Excel side I'm afraid it can be too much data for MS Excel.
Thank you for your opinion.
Luke
July 16, 2010
Offline
Hi Lukasz,
If it's pivoting you need, then won't a PivotTable do the job? See attached. Note: I don't know what M_3, M_4 etc. relate to. You may need to add another column that classifies each row into its relevant 'M' code. For now I've used the document number across the columns.
Mynda
June 17, 2020
Offline
M_0 to M_11 are months. M_0 is present month (March 2021) and M_11 is present plus 11 months (February 2022). Columns names can be 0 to 11 or 1 to 12. It doesn't matter. What matters is the quantity that have to be in the right months. There are many orders in one month, so it won't work this way. I know this method (simple pivot table).
July 16, 2010
Offline
Hi Lukasz,
Thanks for clarifying. I think there is an error with example for article 9913 because this should be month 6 of 2022, assuming the month is derived by taking the planned ready to shipment date + the Shipment Time. If so, I think the PivotTable in the attached file gives you what you want.
Mynda
1 Guest(s)
