How to update a defined named range when data is refreshed

[Anonymous]

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?

[edhans]

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.

[Anonymous]

@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.

[edhans]

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. 

[Anonymous]

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. 

[edhans]

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:

1. Removes the top 3 rows. Keep or kill that line if desired.
2. 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.

[Anonymous]

@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.

[edhans]

@Anonymous you still have not given me good sample data. I will try this one more time by guessing what your stuff is doing. After this, if this still doesn't work, post files via dropbox or onedrive.

I created 2 CSV files that look like this, they have different dates in the text field in cell C4. (CSVs don't have cells, but you get the point, 3rd column, 4th row.

1. Get Data From Folder
2. Point to the folder the CSV files are in
3. Select Transform Data
4. Make sure only the CSV files you want are shown in the directory, then click COMBINE
   1. 
5. Let it select the first sample file and press ok.
6. It will return something that looks like this:
   1. 
7. You have a Transform Sample Query step. Go edit that.
   1. 
8. In that Transform Sample File, add a new column with the Following Code: Date.From(Text.Middle([Column3], 23)). Get rid of Date.From() if your data isn't a legit date and you just want the text.
9. Go back to your original query and it will now look like this:
   1. 

If that is still not what you need, post files, and an image of the expected results, not more textual explanations, as I am not getting the clear meaning.

[Anonymous]

@edhans I apologize, I have been trying all day to figure out how to upload these files! 

I found this article saying I need to use a DropBox or OneDrive folder.  But I still can't figure out how to upload the link without an actutal email address to share.

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

[edhans]

In OneDrive, just right-click on the file and select Share, then change it to this:

 - or change it to "anyone with link can view" by clicking on that and unchecking the "Allow editing" button.

But if you cannot do that, your org has locked down sharing without an email address. You can PM me and I will share my email address with you and I'll get the files that way. I'll still respond in the forum, but at least I'll have the files to tinker with.

[Anonymous]

@edhans Try this:

 https://udfinc-my.sharepoint.com/:f:/p/cday/EifgTZ8sLGZLqp92Gw6oCkMBwEk-xoFRs7JQ3dkeguinSA?e=akXob1 

The Combined Data.xlsx is where all the Raw Data is brought together.

The "Append" Tab is the final outcome

The "Raw Data" Tab is where I'm currently using the named cell range for all of the .csv files.

Obvioulsy I could just update that Tab.  But didn't know if there were a cleaner / easier way to not do this manually

[edhans]

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.

[PhilipTreacy]

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.

[Anonymous]

Hi @PhilipTreacy 

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.

I am just starting to work with Power Query, and taking it one step at a time to better understand the process.

Each .CSV workbook has 1 worksheet.  The worksheet will be appended with other workbooks that have 1 worksheet.

I'm using the dates in cell A4 to add a column in each workbook, so the time period shows the separation in data.

For example, my current macro is:

4wk workbook, will have a column added: "Latest 4 Wks - Ending " & Left(Right(.Range("A4"), 24), 23)"

13wk workbook, will have a column added: "Latest 13 Wks - Ending " & Left(Right(.Range("A4"), 24), 23)"

However, if I can just use Power Query, I wouldn't have to run the macro to append the data every month.

Yes, I can create the name range when I export the .CSV workbook and save over the last months workbook, but I didn't know if there was a way to automate this and save time

[edhans]

@Anonymous - did you look at my code? It pulls whatver is in cell A4 with no need for range names. If it didn't help, please clarify on what you need that my code sample didn't do and I will try to assist further.