Forum Discussion
How to update a defined named range when data is refreshed
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
Custom
For 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.
No. PowerQuery can see a range name, but cannot set one. Say you have a workbook that looks like this:
C4 has the data you want, and there is stuff in A1, and perhaps other cells. The following M code will get C4, then do the Add Columns function you want.
let
Source = Excel.Workbook(File.Contents("C:\Users\UserPath\OneDrive - eHansalytics\Book1.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(Sheet1_Sheet,{Table.ColumnNames(Sheet1_Sheet){2}}),
#"Removed Top Rows" = Table.Skip(#"Removed Other Columns",3),
#"Added Custom" = Table.AddColumn(#"Removed Top Rows", "Custom", each Text.Middle([Column3],23)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column3"})
in
#"Removed Columns"Let me know if you have additional questions.
I understand. I'm trying to follow your code below.
Does this line mean 2 columns are being removed (Columns: A & B)?
#"Removed Other Columns" = Table.SelectColumns(Sheet1_Sheet,{Table.ColumnNames(Sheet1_Sheet){2}}),I don't want to remove any data columns though.
Yes. You can ignore that if you want. Your expected results were not clear. It is not removing columns 1 and 2 technically, it is getting the name of the 3rd column (Power Query indexes at 0, so asking for column 3 is a 2) and the selecting column 2 only.
Try this code Anonymous
let Source = Excel.Workbook(File.Contents("C:\Users\UserPath\OneDrive - eHansalytics\Book1.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Removed Top Rows" = Table.Skip(#"Sheet1_Sheet",3), #"Extracted Text Range" = Table.TransformColumns(#"Removed Top Rows", {{Table.ColumnNames(#"Removed Top Rows"){2}, each Text.Middle(_, 23), type text}}) in #"Extracted Text Range"This:
- Removes the top 3 rows. Keep or kill that line if desired.
- Extracts the chars starting at 23 and later of the 3rd column. There is actually no need to add a column then remove the source column. This does it in one step.
becomes:
If this is not exactly what you need, please give a VERY clear example of the source data and the expected output. I think the whole range name issue is confusing the matter. In other words, don't tell me how you would like to approach it. Tell me what the end result should be and let me suggest a good way to approach it given the capabilities of Power Query itself.
edhans Thank you for your paitence on this!
The code you provided is not letting me past the Syntax error: "Expression.SyntaxError: Token Comma expected."
Scenario:
I pull the data from a reporting system every month, export the workbook as a .CSV, and save it over the last month's workbook.
Each .CSV workbook has 1 worksheet full of data starting from A1 thru Z.
Each .CSV worksheet has the same style headings from A1:A17
There are a total of 7-10 of these workbooks that I pull, and the goal is to have Power Query Append all of the worksheets together.
I want to use the date range in cell "A4" to add a column in each worksheet, so the time period shows the separation of data.
For example, my current macro is:
The 4wk workbook, will Add a column
With .Cells(.Rows.Count, "B").End(xlUp).Offset(1).Resize(UBound(Data(0))) .Offset(0, -1).Value = "Latest 4 Wks - Ending " & Left(Right(.Range("A4"), 24), 23)The 13wk workbook, will have a column added to Column 1:
With .Cells(.Rows.Count, "B").End(xlUp).Offset(1).Resize(UBound(Data(0))) .Offset(0, -1).Value = "Latest 13 Wks - Ending" & Left(Right(.Range("A4"), 24), 23)"The goal is if I can set this all up with Power Query, I wouldn't have to run the macro to append the data every month; I would just need to refresh the power query.