Power Query
Power Pivot
July 11, 2016
Hi,
I am trying to create a query that combines named ranges in a workbook. I am using named ranges rather than tables because the column headings are formulas and these aren't allowed in tables. The column headings show the month and year. The formula in each is based on the previous column header and uses edate([previous column],1) to increment the month. The months work on a rolling basis so in the example below it starts in Oct 2017 and then shows Nov & Dec but next month it will start in Nov and show Dec & Jan.
The data in each column is basically stock forecasts for products. It looks something like this:
Customer | SKU | Opening Stock | Oct-17 | Nov-17 | Dec-17 | 3 Month Total | Total less SOH |
Acme Inc | Widget 1 | 12 | 5 | 24 | 6 | 35 | 23 |
Acme Inc | Widget 2 | 15 | 15 | 26 | 17 | 58 | 43 |
There is a table for each customer and I need to combine them all to get a summary report.
I can create the query to combine the tables but when it picks up the headers it converts them to text fields and they display as follows:
Customer | SKU | Opening Stock | 01/10/2017 00:00:00 | 01/11/2017 00:00:00 | 01/12/2017 00:00:00 | 3 Month Total | Total less SOH |
Is there any way to get the query to recognise the content of the header rows as a date field and display it in a similar way to the source data ranges ie. mmm-yy?
Thanks
Mark
1 Guest(s)