exceluploadpowerquerym

Problem getting the value from a cell using M in excel

I have a series of reports written in Excel. In these reports, there is an specific sheet called "My_Sheet" where in the position G3 there is a number.

I am constructing global report (also written in excel) in which I have the number associated to each report as a column.

I have used Code_Copilot to generate a function since I am no expert on M language.

`let
    // Load the files from the folder
    Origen = Folder.Files("C:\Users\J\MyFolder"),

// Filter hidden files
#"Filter hidden attributes" = Table.SelectRows(Origen, each [Attributes]?[Hidden]? <> true),

// Add a function to extract G3
#"Invocar función para extraer G3" = Table.AddColumn(#"Filter hidden attributes", "G3 Value", each 
    let
        // Load the content of the Excel file
        Source = Excel.Workbook([Content], true), // Open the file
        // Select the sheet that contains the specific table
        MySheet_Sheet = Table.SelectRows(Source, each ([Name] = "My_Sheet")), 
        
        // Check if the sheet exists
        G3_Value = if Table.RowCount(MySheet_Sheet) > 0 then
            let
                // Get the content of the sheet
                SheetContent = MySheet_Sheet{0}[Content],
                // Extract the value from cell G3 (third row, seventh column)
                ValueG3 = try SheetContent{2}[Column7] otherwise "No data"
            in
                ValueG3
        else
            "Sheet not found" // If the sheet doesn't exist
    in
        G3_Value
),

// Select only the columns with the file name and G3 value
#"Select final columns" = Table.SelectColumns(#"Invocar función para extraer G3", {"Name", "G3 Value"})
in
    #"Select final columns"
`

This, however, returns that no data is detected.

enter image description here

The data looks like this, that is the My_Sheet enter image description here

Thus, a value of 3 should be expected at least in the first image in the row associated to the second screenshot.

Any answer is appreciated. Best regards.

Solution

  • The column in MySheet_Sheet is actually called Data not Content, and it seems to automatically promote headers in my tests so you actually want row 2 of the resulting table:

                SheetContent = MySheet_Sheet{0}[Data],
            // Extract the value from cell G3 (second row, seventh column due to first row promotion)
            ValueG3 = try SheetContent{1}[Column7] otherwise "No data"