SOLVED

Power Query File Name Drop Down

%3CLINGO-SUB%20id%3D%22lingo-sub-1523993%22%20slang%3D%22en-US%22%3EPower%20Query%20File%20Name%20Drop%20Down%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1523993%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20going%20to%20do%20my%20best%20to%20describe%20this.%26nbsp%3B%20I%20would%20like%20to%20create%20a%20parameter%20query%20where%20the%20user%20selects%20a%20filename%20and%20the%20table%20will%20display%20based%20on%20what%20the%20user%20selects%20from%20the%20drop%20down.%26nbsp%3B%20I%20know%20that%20I%20will%20probably%20need%20to%20concatenate%20the%20parameters%20I%20created.%26nbsp%3B%20Let%20me%20give%20you%20an%20example.%26nbsp%3B%20On%20the%20Song%20worksheet%2C%20in%20the%20Anyone.xlsx%20file%2C%20the%20Anyone%20Table%20is%20displayed.%26nbsp%3B%20If%20the%20user%20selected%20Elderly.xlsx%20from%20my%20file_name%20drop%20down%20table%20I%20want%20the%20Elderly.xlsx%20table%20to%20display%20on%20the%20worksheet.%26nbsp%3B%20If%20you%20have%20any%20suggestions%20or%20can%20point%20me%20to%20a%20blog%20post%20that%20would%20help%20me%20answer%20this%20question.%26nbsp%3B%20I%20would%20greatly%20appreciate%20the%20help.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1523993%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1524036%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20File%20Name%20Drop%20Down%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1524036%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F434928%22%20target%3D%22_blank%22%3E%40lmk001%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20file%20and%20table%20names%20are%20the%20same%20you%20may%20add%20named%20cell%2C%20let%20say%20%3CSTRONG%3EfileTableName%3C%2FSTRONG%3E%2C%20in%20your%20workbook%20with%20it's%20name%2C%20after%20that%20it's%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20getFileTableName%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22fileTableName%22%5D%7D%5BContent%5D%7B0%7D%5BColumn1%5D%2C%0A%20%20%20%20Source%20%3D%20Excel.Workbook(File.Contents(%22C%3A%5CTest%5C%22%20%26amp%3B%20getFileTableName%20%26amp%3B%20%22.xlsx%22)%2C%20null%2C%20true)%2C%0A%20%20%20%20Table1_Table%20%3D%20Source%7B%5BItem%3DgetFileTableName%2CKind%3D%22Table%22%5D%7D%5BData%5D%0Ain%0A%20%20%20%20Table1_Table%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ejust%20change%20the%20folder%20name%20as%20necessary.%20With%20drop-down%20list%20it's%20the%20same%2C%20named%20cell%20will%20be%20one%20with%20drop-down%20list.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I'm going to do my best to describe this.  I would like to create a parameter query where the user selects a filename and the table will display based on what the user selects from the drop down.  I know that I will probably need to concatenate the parameters I created.  Let me give you an example.  On the Song worksheet, in the Anyone.xlsx file, the Anyone Table is displayed.  If the user selected Elderly.xlsx from my file_name drop down table I want the Elderly.xlsx table to display on the worksheet.  If you have any suggestions or can point me to a blog post that would help me answer this question.  I would greatly appreciate the help. 

1 Reply
Highlighted
Best Response confirmed by lmk001 (Occasional Contributor)
Solution

@lmk001 

If file and table names are the same you may add named cell, let say fileTableName, in your workbook with it's name, after that it's like

let
    getFileTableName = Excel.CurrentWorkbook(){[Name="fileTableName"]}[Content]{0}[Column1],
    Source = Excel.Workbook(File.Contents("C:\Test\" & getFileTableName & ".xlsx"), null, true),
    Table1_Table = Source{[Item=getFileTableName,Kind="Table"]}[Data]
in
    Table1_Table

just change the folder name as necessary. With drop-down list it's the same, named cell will be one with drop-down list.