SOLVED

Get data from multiple sharePoint folder

%3CLINGO-SUB%20id%3D%22lingo-sub-1955582%22%20slang%3D%22en-US%22%3EGet%20data%20from%20multiple%20sharePoint%20folder%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1955582%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20similar%20worksheets%20in%20different%20SharePoint%20folder%2C%20that%20I%20would%20like%20to%20combine%20into%20one.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20manage%20to%20make%20a%20new%20table%20for%20every%20worksheet%2C%20is%20it%20possible%20to%20merge%20all%20into%20one%20table%2C%20with%20power%20query%20(get%20Data)%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20Regards%3C%2FP%3E%3CP%3E-%20Geir%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1955582%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1956664%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20data%20from%20multiple%20sharePoint%20folder%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1956664%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9195%22%20target%3D%22_blank%22%3E%40Geir%20Hogstad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20use%20this%20connector%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20420px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237755iDD5FC2D973F88DEF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIt%20works%20the%20same%20way%20as%20From%20Folder%20connector%20on%20file%20system.%20Perhaps%20some%20adjustments%20will%20be%20required%2C%20but%20it%20very%20depends%20on%20your%20data.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1960324%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20data%20from%20multiple%20sharePoint%20folder%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1960324%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BFrom%20SharePoint%20folder%20is%20not%20an%20option%20in%20my%20Excel.%20Maybe%20wrong%20license%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20use%20Internet%2C%20create%20a%20worsheet%20for%20each%20workbook%2C%20then%20merge%20the%20tables%20in%20a%20second%20step.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20it%20would%20be%20nice%2C%20if%20it%20was%20possible%20to%20get%20all%20into%20one%20table%20with%20combined%20queries.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20result%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22merge%20tables.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F238095i7A4FC6BB60B694C2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22merge%20tables.PNG%22%20alt%3D%22merge%20tables.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20Regards%3C%2FP%3E%3CP%3E-%20Geir%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1960526%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20data%20from%20multiple%20sharePoint%20folder%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1960526%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9195%22%20target%3D%22_blank%22%3E%40Geir%20Hogstad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20shall%20not%20return%20each%20of%20Mal%2C%20Mal%20(2)%20etc%20into%20Excel%20sheet.%20Use%20them%20as%20connections%20only.%20Append%20them%20to%20each%20other%20into%20new%20resulting%20query%20which%20return%20back%20to%20Excel.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20548px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F238104i966536408D95B477%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EBefore%20appending%20most%20probably%20you%20need%20to%20clean%20individual%20tables%20-%20remove%20empty%20and%20unnecessary%20rows%2C%20add%20headers%2C%20etc.%20You%20may%20do%20that%20manually%20one%20by%20one%2C%20or%2C%20if%20all%20tables%20have%20the%20same%20structure%2C%20automate%20a%20bit%20moving%20all%20workbooks%20into%20separate%20Sharepoint%20folder%2C%20connect%20it%20using%20connector%20I%20mentioned%20before%2C%20make%20necessary%20transformation%20on%20the%20sample%20only%20and%20combine%20all%20together.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Regular Contributor

Hi,

 

I have a similar worksheets in different SharePoint folder, that I would like to combine into one. 

 

I manage to make a new table for every worksheet, is it possible to merge all into one table, with power query (get Data)?

 

Best Regards

- Geir

5 Replies

@Geir Hogstad 

You may use this connector

image.png

It works the same way as From Folder connector on file system. Perhaps some adjustments will be required, but it very depends on your data.

Thank you @Sergei Baklan From SharePoint folder is not an option in my Excel. Maybe wrong license?

 

I can use Internet, create a worsheet for each workbook, then merge the tables in a second step.

 

But it would be nice, if it was possible to get all into one table with combined queries.

 

Here is the result:

 

merge tables.PNG

 

 

Best Regards

- Geir

best response confirmed by Geir Hogstad (Regular Contributor)
Solution

@Geir Hogstad 

You shall not return each of Mal, Mal (2) etc into Excel sheet. Use them as connections only. Append them to each other into new resulting query which return back to Excel.

image.png

Before appending most probably you need to clean individual tables - remove empty and unnecessary rows, add headers, etc. You may do that manually one by one, or, if all tables have the same structure, automate a bit moving all workbooks into separate Sharepoint folder, connect it using connector I mentioned before, make necessary transformation on the sample only and combine all together.

Thank you @Sergei Baklan 

 

It worked perfect.

 

Best Regards

- Geir

@Geir Hogstad , you are welcome