SOLVED

Need to extract values from named cells (Get and Transform)

Brass Contributor

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

23 Replies
- will it be an issue that I use a Norwegian Version of Excel 365?
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 + T
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?

@JEMagnussen 

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.

image.png

I copy your source file and test with folder c:\test. Please check attached.

@JEMagnussen 

To my knowledge editor with Intelligent so far is only for insiders channel - Insiders Fast and Monthly Targeted (aka Insiders Slow)

@Abiola1 

 

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?

@Sergei Baklan 

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

OK, let's hope they will release Intellisense soon to all users… :)

@JEMagnussen

 

This is a screenshot of an earlier attemt that failed due to an [DataFormat.Error] File contain corrupted data...

 

 2019-11-29_16-44-22.jpg

@JEMagnussen 

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)

 

Hi,
I have tried to insert the correct file path in the Filepath cell, but I get an error saying: " The external table has an unexpected format" and I still can't use the editor as it Locks up.


The reult form the file you uploaded is not quite the same as shown in the screenshot I send over earlier, what could be the reason for this?

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

@Sergei Baklan 

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

It seems like the Query does not exclude all files that are not .xlsx files, at least I now also found an error: The external table does not have expected format, details: xxxxxxx.JPG

Best regards
Jesper

@JEMagnussen 

Please remove the last back slash in filepath

image.png

 

@Sergei Baklan 

 

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

@JEMagnussen 

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

image.png

Is that correct or you use only first one?

@JEMagnussen 

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

Hi again,
We use a seperate folder for each file, as this is a Collection of files that belong to that particular change-order. So far there are some 65+ folders with .xlsx files (plus various other files) inside that we need to Query for the result of the 3 named cells in each file.
So it´s correct that the files are in a lot of different folders under the main folder /EM

Best regards
Jesper
1 best response

Accepted Solutions
best response confirmed by JEMagnussen (Brass Contributor)
Solution

@JEMagnussen 

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)\EM

Attached file works if another 3 folders are under above one.

View solution in original post