Power Query Custom Column

[jstevens]

I'm having a challenge resolving a formula in a custom column. Expression.Error: The name 'Range' wasn't recognized. Make sure it's spelled correctly.

EL_95.PNG

[Rudi]

Hi John.

That is not a valid formula in PQ.
No Range function exists in PQ and the syntax is also incorrect. You cannot refer to a field in double quotes.

Can you either give more details about what you are trying to calculate or provide a sample workbook that contains source data and your query so I can understand the content of this calculation and assist.

TX.

[jstevens]

Rudi,

I came to the conclusion that a Range function did not exist in PQ. What works is ...

Code: Select all

Sheets("Sheet1").ListObjects("MyTable").DataBodyRange.Columns(1).Resize(, 1) = Range("R_Month")

PQ is very versatile and thank you for mentioning it in one of your previous posts.

[Rudi]

Hi John,

Glad to hear your comments on PQ. It is a great product.

I am a bit confused though as the code you post has nothing to do with PQ. This is a VBA code.

It seems your question is resolved, so no need to add anything more from my side.

[jstevens]

Rudi,

Since there is no Range function in PQ there is nothing else you need to do on your side. I went with a VBA solution which in the end updated the appropriate table records.

[rory]

I suspect you could achieve the relevant effect in PQ directly but, as Rudi said, you'd need to provide a workbook so we can see what's going on.

[jstevens]

Rory/Rudi,

Here is a sample workbook and flat file for the table. I have included notes within the workbook.

el_Table.xlsm

myTextFile.txt

[rory]

You can create a new query that just returns the range using:

= Excel.CurrentWorkbook(){[Name="R_Month"]}[Content]

and call it say Range.

Then create a custom column in your existing query using just =Range as the formula, then expand the table.

[jstevens]

Thank you Rory!

I got it to work with one minor modification to the custom column which I added to the existing query.

EL_97.PNG

Range = New query named "Range"
[Month] = Field heading in query
{0} = 1st record in query connection results.

[Rudi]

Glad its working John.