Ms excel qry 2016

Hi Experts using excel 2016

I have six workbooks each with one worksheet. I want to combine ie create a query where I link each workbook to the other using a primary key..how do I do it..
route217Asked:
Who is Participating?
 
abbas abdullaCommented:
Hi,

You mentioned that your files contains one sheet, but the file you've attached is containing 3 sheets with different headers in each sheet. Anyway to merge excel files from folder together using Power Query you can use the below code.
1.  Go to Data Tab.
2. Choose New Query > From Other Sources > Blank Query
3. In the Query Editor Go to Advanced Editor.
4. Paste the code below in the Advanced Editor (Remove everything written in the editor before pasting).
5. Change the source folder path to be the path in your PC or Shared drive.

let
    Source = Folder.Files("C:\Users\abbasabdulla\Downloads\MergeXlFiles"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Content"}),
    #"Exclude the opened files" = Table.SelectRows(#"Removed Other Columns", each not Text.Contains([Name], "~")),
    MakeContentAsTable = Table.AddColumn(#"Exclude the opened files", "MakeContentAsTable", each Excel.Workbook([Content])),
    RemoveAllOtherColumns = Table.RemoveColumns(MakeContentAsTable,{"Name", "Content"}),
    #"Expanded MakeContentAsTable" = Table.ExpandTableColumn(RemoveAllOtherColumns, "MakeContentAsTable", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),
    PromoteHeadersOfData = Table.AddColumn(#"Expanded MakeContentAsTable", "MyDataWithHeaders", each Table.PromoteHeaders([Data])),
    FilterOnlySheet1 = Table.SelectRows(PromoteHeadersOfData, each ([Name] = "Sheet1")),
    #"RemoveOtherColumnsExcept MyDataWithHeaders" = Table.SelectColumns(FilterOnlySheet1,{"MyDataWithHeaders"}),
    #"Expanded MyDataWithHeaders" = Table.ExpandTableColumn(#"RemoveOtherColumnsExcept MyDataWithHeaders", "MyDataWithHeaders", {"Co,mments", "ID", "Project Description", "Project Go Live", "Project Name", "Project Start Date"}, {"Co,mments", "ID", "Project Description", "Project Go Live", "Project Name", "Project Start Date"})
in
    #"Expanded MyDataWithHeaders"

Open in new window

0
 
Rob HensonFinance AnalystCommented:
You will need to expand on your requirement to get some answers as there are numerous ways you can link files.

Can you upload a couple of the files and illustrate what fields are to be linked? Might be easier to demonstrate in one file with multiple sheets.
0
 
route217Author Commented:
Hi Rob thanks for the feedback.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
route217Author Commented:
Hi Rob...

Test file uploaded just sample.data nothing else..
test_file.xlsx
0
 
route217Author Commented:
Need to connect project is to project is and project name to name...etc..
1
 
route217Author Commented:
Thanks Rob...what other methods are available for learning...
0
 
Rob HensonFinance AnalystCommented:
It would help if you explain the correlation between the fields on each sheet. Just looking at it there does not seem to be any.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.