Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI 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.
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:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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.
@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.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @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.
Proud to be a Super User!
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
@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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
49 | |
19 | |
12 | |
11 |