Power Query Function Date to Custom Column

0
votes

I would like to include the contents of a named range in a custom column in a power query.

I have created a named range cell in excel which holds a date value, then created a blank query with Function and am calling that into a custom column in another query - all based on this users question/answer How can I reference a cell's value in a Power Query.

When trying to call the function (named range) into the custom column in my query I am receiving an Expression.Error:

enter image description here

I have tried Date.ToText, formatting the column in Transform -> Data Type and receive the same error but with different messages, eg. instead of #datetime it would be value.

This is my line of code in advanced editor that is calling the named range (date).

 #"Added Custom1" = Table.AddColumn(#"Filtered Rows2", "Date2", each Excel.Workbook(File.Contents(GetDate("FNEndDate"))))
1
excelpowerquery
So what you are actually doing is trying to add a column to your table in which each row is a nested table with the contents of an Excel of which the path+file name is a date/time.... That won't work. You'd better try and explain what you really want to achieve. – MarcelBeug
@MarcelBeug I have edited my original question. Hope this is a bit clearer. The link to the question shows the query for the function which is being used to call the named range cell into my power query custom column. – Preena

1 Answers

1
votes

If you just want to add a column with the value from the named range, just remove the Excel.Workbook(File.Contents part:

#"Added Custom1" = Table.AddColumn(#"Filtered Rows2", "Date2", each GetDate("FNEndDate"))