Forum Discussion

JEMagnussen's avatar
JEMagnussen
Brass Contributor
Nov 29, 2019
Solved

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="...
BI & Data Analysis
excel
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    Nov 29, 2019

    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.

JEMagnussen's avatar
JEMagnussen
Brass Contributor
Nov 29, 2019
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
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 29, 2019

JEMagnussen 

Please remove the last back slash in filepath

 

  • JEMagnussen's avatar
    JEMagnussen
    Brass Contributor
    Nov 29, 2019

    SergeiBaklan 

     

    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

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Nov 29, 2019

      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

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Nov 29, 2019

      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

      Is that correct or you use only first one?

      • JEMagnussen's avatar
        JEMagnussen
        Brass Contributor
        Nov 29, 2019
        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

Resources