Power Query Function Date to Custom Column

Question

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:

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"))))

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, Commented Aug 17, 2017 at 9:53
@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
– Preena, Commented Aug 18, 2017 at 0:08

MarcelBeug · Accepted Answer · 2017-08-18 08:14:53Z

2

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"))

answered Aug 18, 2017 at 8:14

MarcelBeug

3,0171 gold badge11 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Power Query Function Date to Custom Column

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related