Forum Discussion
How to update a defined named range when data is refreshed
I have a data source that I save over every month. I want to use .Range("A4") for an `.addcolumn` with `text.middle`.
How do I define a named range in a worksheet, without having to resave the named range everytime I save over the data?
14 Replies
- edhans
Community Champion
Can you be more clear on your response? Power Query doesn't understand Cell References like A4, and I am not sure what a .addcolumn is, unless you are referring to Table.AddColumn() but that requires a table in put, not a cell range.
edhans , sorry that my question was unclear.
I have a current m code:
let Source = Excel.CurrentWorkbook(){[Name="cellA4"]}[Content], #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Middle([Column1],23)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}), Custom = #"Removed Columns"{0}[Custom] in CustomFor this to work, I need to define the range: A4 in my worksheet, which is `cellA4`
I pull new data every month, and save over the last worksheet. I would like to know if there is a way to keep the defined name range: "A4" when I save over the worksheet. Or even an M code to reset the named range, without me manually setting it?
I hope this is more clear.
- PhilipTreacy
Super User
Hi Anonymous
You say you pull new data every month - are you describing the getting of data from the workbook?
How does this workbook get created? Can you not create the named range at the same time as creating the workbook? It's just 1 more thing to do.
That said, if you are loading data from a single cell, why use a workbook? Save the data into a text fle or CSV and avoid the need for a named range. Or maybe I don't have the full picture here?
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
