Forum Discussion
Need to extract values from named cells (Get and Transform)
Hi,
I have made an Get and Transform Query on a folder with many subfolders all containing an workbook with the first sheet named "Endringsmelding" in each. These sheets contains 3 named cells: K5="EM", B11= "Beskrivelse" and L94= "Total".
I need to get those values in the named cells out and presentet in the workbook "Test" that I have Attached.
I have run the Get and Transfom Query, have the right folder as Source, so far so good. My problem is that I do not find any Reference to how to add New Columns with the values from the named cells?
Any help will be greatly appreciated.
Best regards
Jesper
If so your filepath shall be
C:\Users\Jema\Veidekke\NO-ENT-GLENCORE Cu-Bygg - Teamdokumenter\Intern Prosjekt Dokumenter\10 Økonomi\10.04 Endringer\Endringsmeldinger (EM)\EMAttached file works if another 3 folders are under above one.
23 Replies
As variant:
- create custom function which extract all named values from defined file:
(pFilePath as text) => let Source = Excel.Workbook(File.Contents(pFilePath), null, true), DefinedNamesOnly = Table.SelectRows( Source, each ([Kind] = "DefinedName") ), ExpandThem = Table.ExpandTableColumn( DefinedNamesOnly, "Data", {"Column1"}, {"Column1"} ), RemoveUnused = Table.SelectColumns( ExpandThem, {"Name", "Column1"} ), TransposeTable = Table.Transpose(RemoveUnused), PromotHeaders = Table.PromoteHeaders( TransposeTable, [PromoteAllScalars=true] ), ProperType = Table.TransformColumnTypes( PromotHeaders, { {"Beskrivelse", type text}, {"EM", Int64.Type}, {"Total", Int64.Type} } ) in ProperTypeIn another query use From folder connection, add column with full path\filename for each file, when another custom column with call of above function with previous value as parameter. Remove all other columns and expand this one.
I copy your source file and test with folder c:\test. Please check attached.
Hi Sergei,
I'm sure Your suggestion Works, but for unknown reason the editor freezes when I try to open the Query to change the file path.
If I have an empty file, I'm also not able to use the editor.
I uploaded a New files, as the first file did not have any table in it.
The New file has the right file path, colud you please try to insert the correct file path and upload it again then I can test it.
Best regards
Jesper
In attached file I added FilePath table to have filepath as parameter. It extracted within the script as
let SourcePath = Excel.CurrentWorkbook(), GetFolderPath = Table.SelectRows(SourcePath, each ([Name] = "FilePath")){0}[Content]{0}[FilePath], Source = Folder.Files(GetFolderPath),I guess in your case that will be
C:\Users\Jema\Veidekke\NO-ENT-GLENCORE Cu-Bygg - Teamdokumenter\Intern Prosjekt Dokumenter\10 Økonomi\10.04 Endringer\Endringsmeldinger (EM)\EM\EM001 - Tilleggsarbeid med kranfundament(without the slash at the end compare to your test file)
- By the way, is there any reason why I do not get any Intellisense help when using the Advanced editor for the queries? A setting maybe?
To my knowledge editor with Intelligent so far is only for insiders channel - Insiders Fast and Monthly Targeted (aka Insiders Slow)
- OK, let's hope they will release Intellisense soon to all users… 🙂
- Hello using Norwegian version of Excel 365 doesn't have issue with what you're trying to do....
However, there is only one sheet with data in the first Excel workbook uploaded and use question wasn't clear enough.
To use Power Query,your data must be arranged in the Excel way and converted using CTRL + TI have Attached a testfile2 where the data tab is an Excel table.
Inside each of the listed files, is a worksheet with 3 named cells. I need to list the values of those 3 named cells in the worksheet "testfile2". Prefably the named cells (ranges?) should be dynamic, so user can insert an extra row without corrupting th result.
Am I making any sence?
- - will it be an issue that I use a Norwegian Version of Excel 365?
