How to connect SharePoint 2016 onpremise?

[avi081265]

Hello,

How to connect SharePoint 2016 on-premise document library, fetch the excel from all the folders and combine it?

I tried same thing in SharePoint Online and it is working without any issue.

I am struggling to featch information from SharePoint 2016 On-Premise. However I am able to fetch the list of excel from a document library, but not able to combine this excel and transform the excel data.

Please advise.

Thanks

Avian

[Jimmy801]

Hello @avi081265 

if you have a list of Excel-files what is stopping you from combining them? Can you make some screenshot?

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[avi081265]

Hello Jimmy

I am yusing ODI Datafeed using http://siteurl/_vti_bin/listdata.svc. I selected my document library and then click on transform. Then I can see list of excel, but did not found Combine, Please see below screen shot

Let me know if I am missing anything. I tried to use SharePoint Folder option, but not able to connect SharePoint 2016 on prem site.

Regards

Avian

[Jimmy801]

Hello @avi081265 

I suppose you have somewhere the complete path of your Excel-files. In this case add a new column where you accessing sharepoints data using the path of your files as input and then use Excel.WorkBook to transform the data. 

By the way, what connector are you using to access your data?

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[avi081265]

Finally, I found  out the problem. The problem is that in each excel top 2 rows are user a common header like "Finance Register - France" and these rows are merged. and becuase of this date column for all other excel are showing is empty. If remove these top two rows, thne everything works fine.

I also try to remove top two rows from query output by selected Remove Top Rows after combining excel, but it remove top two rows of first excel, in rest of the combined excel I can see the null values.

Is there any other options to remove top two rows from all excelx before combine or after combine? 

[Jimmy801 · Accepted solution]

Hello @avi081265 

to remove the first 2 rows of your table you can use Table.Skip(YourTable,2).

But you have to apply this function before you are combining your tables. So basically when you have your list of files and then applying a function of reading first your workbook, then you navigate to your sheet and most probably you promote your headers (until you are using tables). Exact after this step, you have to apply Table.Ski

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy