Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 11 to 20 of 21

Thread: Import Dynamic Workbook

  1. #11
    Seeker Belebala's Avatar
    Join Date
    Jan 2019
    Posts
    13
    Articles
    0
    Excel Version
    2016


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

    I got a question, why my query fnGetParameter display the table icon not function icon? Did I do something wrong?

    Click image for larger version. 

Name:	2019-01-30_12-38-27.jpg 
Views:	5 
Size:	4.4 KB 
ID:	8833

  2. #12
    Conjurer Paul_Christie's Avatar
    Join Date
    Mar 2016
    Location
    Nottingham, UK
    Posts
    152
    Articles
    0
    Excel Version
    Office 365 Monthly update
    Without being able to watch what you were doing I can't be sure. What I did was create a blank query, pasted the function code in, named the function as fnGetParameter and saved it as connection only.

    Paul

  3. #13
    Seeker Belebala's Avatar
    Join Date
    Jan 2019
    Posts
    13
    Articles
    0
    Excel Version
    2016
    I think I got ParameterTable query working. How do I replace the source file if my import file is an excel workbook?

    I tried this, but it said token identifier expected.
    Source = Excel.Workbook(fnGetParameter("FilePath")&fnGetParameter("FileName")),null, true),
    Attached Files Attached Files

  4. #14
    Conjurer Paul_Christie's Avatar
    Join Date
    Mar 2016
    Location
    Nottingham, UK
    Posts
    152
    Articles
    0
    Excel Version
    Office 365 Monthly update
    The source line needs to look like this

    Source = Excel.Workbook(File.Contents(fnGetParameter("FilePath")&fnGetParameter("FileName")), null, true),

    Paul

  5. #15
    Seeker Belebala's Avatar
    Join Date
    Jan 2019
    Posts
    13
    Articles
    0
    Excel Version
    2016
    Now I get the error of :
    Expression.Error: We cannot convert the value "D:\PowerQuery..." to type Function.
    Details:
    Value=D:\PowerQuery\
    Type=Type

    Any idea?

  6. #16
    Conjurer Paul_Christie's Avatar
    Join Date
    Mar 2016
    Location
    Nottingham, UK
    Posts
    152
    Articles
    0
    Excel Version
    Office 365 Monthly update
    Is it possible to post the workbook with the query and the workbook with the data so i can see both ends of what is going wrong?

    Paul

  7. #17
    Seeker Belebala's Avatar
    Join Date
    Jan 2019
    Posts
    13
    Articles
    0
    Excel Version
    2016
    My query is saved under the Dynamic File Test and the data is coming fom the Book1 workbook.
    Attached Files Attached Files

  8. #18
    Conjurer Paul_Christie's Avatar
    Join Date
    Mar 2016
    Location
    Nottingham, UK
    Posts
    152
    Articles
    0
    Excel Version
    Office 365 Monthly update

    Import Dynamic Workbook

    It looks like it had something to do with how you created fnGetParameter

    Book1.xlsxDynamic File Test.xlsx

    Have you got Ken Puls book M is for (Data) Monkey. It really is worth every penny it will cost you.

    Paul

  9. #19
    Seeker Belebala's Avatar
    Join Date
    Jan 2019
    Posts
    13
    Articles
    0
    Excel Version
    2016
    I will definitely grab Ken Puls' book.

    I get it now, if I invoked the fnGetParameter query, it will turn into the query table icon. If I don't invoke it, it will work.

    Thank you very much Paul for solving my problem. I appreciate your effort!

    Belebala

  10. #20
    Acolyte El Cid's Avatar
    Join Date
    Aug 2016
    Location
    Greenville, SC
    Posts
    51
    Articles
    0
    Excel Version
    Excel 2016
    let
    path = Excel.CurrentWorkbook(){[Name="PATH"]}[Content]{0}[Column1],
    filename = Excel.CurrentWorkbook(){[Name="FNAME"]}[Content]{0}[Column1],
    address = Text.Combine({path,filename}, ""),
    Source = Excel.Workbook(File.Contents(address), null, true),



    1. First you make two named ranges in a separate worksheet tab I name PARAM: PATH and FNAME using two separate cells, one right after the other.



    1. Next, place these formulas in each of these named ranges



    1. In PATH and put this formula (which gets the path of the current spreadsheet) in it:


    =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-1)


    1. FNAME and put this formula (to get the name of the current spreadsheet) in it if you are pulling data from it to use in your current spreadsheet.


    =MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]", CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

    OR

    Just type in the name of the file you want to use.


    1. Then, to transfer it to Power Query as a constants, you insert these lines in the PQ editor:


    path = Excel.CurrentWorkbook(){[Name="PATH"]}[Content]{0}[Column1],
    filename = Excel.CurrentWorkbook(){[Name="FNAME"]}[Content]{0}[Column1],

    It loads the named ranges as a (single value) table and gets the first row ({0}) of Column1, i.e., the path and file name of the current sheet. This way, you can load the file using a relative address and you "current file" can change names.


    1. Now you assign the variable "address" to the actual name of your import file combined with the "path" added in step 2.


    address = Text.Combine({path,filename}, ""),

    OR, if you did not use FNAME

    address = Text.Combine({path,"QB QIP.csv"}, ""), //Note that “path” will be the path to the current spreadsheet. If the files are in the same folder, then their paths are “path” as well


    1. This final steps brings it all together and will import the desired file


    Excel File: Source = Excel.Workbook(File.Contents(address), null, true),
    CSV File: Source = Csv.Document(File.Contents(address),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),

Page 2 of 3 FirstFirst 1 2 3 LastLast

Posting Permissions

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