Results 1 to 4 of 4

Thread: Modifying the MCode for first sheet instead of named sheet

  1. #1
    Neophyte rocaleon's Avatar
    Join Date
    Feb 2019
    Location
    California
    Posts
    3
    Articles
    0
    Excel Version
    2013 x64

    Question Modifying the MCode for first sheet instead of named sheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello.
    I followed this blog post to pull data using power query from multiple excel sheets, but am running into an issue.
    It works perfectly except when the sheets have different names. All the files i am using use a date stamp for the sheet name, I am looking for help figuring out how to reference the active worksheet instead of a specified sheet. I tried implementing solutions i found in the comments and other forums but haven't been able to get it to work. And help would be greatly appreciated.

    This is the code i was using that worked for the specific sheet that i got from following the blog post:

    Code:
    (filepath)=>
    let
        Source = Excel.Workbook(File.Contents(filepath), null, true),
        #"01_13_1" = Source{[Name="01_13_2019"]}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(#"01_13_1"),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{XXXXXXX})
    in
        #"Changed Type"
    and this is what i tried to get to work but i don't know enough about coding yet to get it to work.


    Code:
    (filepath)=>
    let
        Source = Excel.Workbook(File.Contents(filepath), null, true),
        #"A" = Source{[Name="Activeworkbook.ActiveSheet"]}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(#"A"),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{XXXXXXX})
    in
        #"Changed Type"

    but it throws this error:

    Code:
    An error occurred in the ‘’ query. DataFormat.Error: External table is not in the expected format.
    Details:
        NFB 01_13_2019 5001.xlsb

  2. #2
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    78
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    There's no concept of Activeworkbook or ActiveSheet

    You could reference a specific sheet # such as
    Code:
    Source = Excel.Workbook(File.Contents(filepath), null, true),
    FirstSheet = Table.SelectRows(Source, each [Kind] = "Sheet"){0}[Data]
    or you could reference a specific excel range that contains some formula that evaluates to what you want to reference, here the content of named range RangeName

    Code:
    RangeValue = Excel.CurrentWorkbook(){[Name="RangeName"]}[Content]

  3. #3
    Neophyte rocaleon's Avatar
    Join Date
    Feb 2019
    Location
    California
    Posts
    3
    Articles
    0
    Excel Version
    2013 x64
    I see. I applied your fix like this but its throwing an error based on Kind?

    Code:
    (filepath)=>
    let
        Source = Excel.Workbook(File.Contents(filepath), null, true),
        Firstsheet = Table.SelectRows(Source, each [Kind] = "Sheet"){0}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(#"Firstsheet"),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{XXXX}})
    in
        #"Changed Type"
    It Says:
    Code:
    An error occurred in the  query. Expression.Error: The field 'Kind' of the record wasn't found.
    Details:
        Name=NFB 01_13_2019
        Data=Table

  4. #4
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    78
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    this code works fine for me:

    Code:
    (filepath)=>
    let Source = Excel.Workbook(File.Contents(filepath), null, true),
    FirstSheet = Table.SelectRows(Source, each [Kind] = "Sheet"){0}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(FirstSheet , [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"a", Int64.Type}, {"b", Int64.Type}, {"c", Int64.Type}})
    in #"Changed Type"
    invoked with

    Code:
    let Source = Function("C:\TEMP\blank.xlsx")
    in Source
    blank2.xlsx
    Last edited by horseyride; 2019-02-08 at 07:55 PM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •