Nov 29 2019 05:30 AM
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
Nov 29 2019 05:31 AM
Nov 29 2019 06:09 AM
Nov 29 2019 06:50 AM
Nov 29 2019 06:51 AM
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
ProperType
In 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.
Nov 29 2019 06:55 AM
To my knowledge editor with Intelligent so far is only for insiders channel - Insiders Fast and Monthly Targeted (aka Insiders Slow)
Nov 29 2019 07:34 AM
I 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?
Nov 29 2019 07:38 AM
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
Nov 29 2019 07:41 AM
Nov 29 2019 07:49 AM
This is a screenshot of an earlier attemt that failed due to an [DataFormat.Error] File contain corrupted data...
Nov 29 2019 08:40 AM
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)
Nov 29 2019 08:49 AM
Nov 29 2019 08:58 AM - edited Nov 29 2019 09:04 AM
Hi again,
As I can find it seems like the Query looks for a folderpath when I insert the correct path, which by the way is a folderpath (a folder with many subfolders containing the workbooks) - but the Query expects a "filepath"! Could this be the problem?
If by any chance we get it to work, the result should prefably look like the screenshot shown a few posts above.
Best regards
Jesper
Nov 29 2019 09:02 AM
Hi Sergei,
Managed to get the editor up and running on the "fx GetFileFields" and here I saw the error, have Attached a screenshot of the error Message.
Best regards
Jesper
Nov 29 2019 09:12 AM
Nov 29 2019 09:25 AM
Nov 29 2019 09:30 AM
Hi Sergei,
I have tried to remove the last backslash, first attempt was running without this backslash - but still get the error with the "filepath" and not "folderpath" as it need to be. It is a real pain in the rear that I can't make modification to the M code as the editor does not work...
Best regards
Jesper
Nov 29 2019 09:46 AM
I duplicated your folder
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
on my computer, Power Query works with 3 test files inside. Please check with attached file.
Checked once more your file and see that all files are in different folders
Is that correct or you use only first one?
Nov 29 2019 09:54 AM
If so take upper folder
C:\Users\Jema\Veidekke\NO-ENT-GLENCORE Cu-Bygg - Teamdokumenter\Intern Prosjekt Dokumenter\10 Økonomi\10.04 Endringer\Endringsmeldinger (EM)\EM
or even more upper
Nov 29 2019 10:07 AM
Nov 29 2019 10:13 AM
SolutionIf 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)\EM
Attached file works if another 3 folders are under above one.