Import Data from several Excel files

[Manuel123]

Dear Helpers,

I bet there is an easy solution for my problem I just don't find.

I have several excel files in a sharepoint online folder and more files will come over time. Now I'd like to import and combine them with the following steps:

1) In the first file I'd like to delete the first 7 rows and put the 8th row as header.

2) All other files that are included in the folder should than be appended but only from row 9 and following

For example:

File 1:

Row 1 to 7	Something weired
Row 8	Header
Row 9 to x	Values

File 2:

Row 1 to 7	Something weired
Row 8	Header
Row 9 to x	Values

Result should then look like:

Row 8 of File 1	Header
Row 9 to x of File 1	Values
Row 9 to x of File 2	Values

Is this possible?

Thank you in advance for your help.

BR
Manuel

[Manuel123]

Thank you @selimovd I will try it out and come back to you asap

[selimovd]

Hey @Manuel123 ,

yes, that is possible. You need to split it into 2 parts:

1. Create a function of the transformations that you want to do. Load an Excel File with the filename as parameter, do all of your transformations and afterwards click with the right mouse button on the query and "Create function".

2. Load the sharepoint folder to another query and filter until only the excel files are left. I think you have to combine Folder Path and File Name to get the complete path. Then you go to Add column and chose "Invocke Custom Function" and chose the function you created in Step 1 and use the folder path and file name combination as a parameter. The function will open every file and do the transformations you described in Step one. Afterwards you can expand that new column so you got all the content in one huge table.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Manuel123]

Hi @selimovd ,

unfortunately it does not fully work. Problem is that I did the first step with an excel file, therefore it expects an path like "C:\Users\..." and now gets a path like "https://xxx.com/sites/xxx".

What do I have to change?

BR
Manuel

[selimovd · Accepted solution]

Hey @Manuel123 ,

that should not be a problem. You can create the function with a local file (C:\...) and later just change the local vs the web content.

After creating the transformations from the local file, the code in the advanced editor should begin like this:

let
    Source = Excel.Workbook(File.Contents("C:\Users\myUser\OneDrive - myCompany\Projects\myFile.xlsx"), null, true),
...

This you can change from File.Contents to Web.Contents:

let
    Source = Excel.Workbook(Web.Contents("C:\Users\myUser\OneDrive - myCompany\Projects\myFile.xlsx"), null, true),
...

Also you have to use the parameter. So you can change both at the same time.

For example create a parameter PathToXLSX:

And then you have to change it to Web.Contents and the parameter:

let
    Source = Excel.Workbook(Web.Contents(PathToXLSX), null, true),
...

Like this you can create the transformations with the local file. Later in the step 2 the parameter will be replaced by the full Sharepoint URL, something like:

https://company-my.sharepoint.com/personal/myUser/Documents/Projects/myFile.xlsx

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Manuel123]

Dear @selimovd ,

this worked perfectly fine. Thank you so much. Maybe you can have a look at another problem I have: Replace Text in all columns but first one 

BR
Manuel