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
Hi Mark,
Try wrapping your EDATE fomula in the TEXT function and convert the header to text with mmm-yy format e.g.:
=TEXT(EDATE([previous column],1),"mmm-yy")
Mynda
Hi Mynda,
That works perfectly. Sometimes when using Power Query I think that everything has to be done there and overlook the usual Excel finctionality!
Thanks
Mark
