Automate creating new queries ...

[adambc]

Forgive me if I don't go into the detail of why I need to do the following, just trust me that there is a good reason!

I have 78 .xlsx files stored in a single folder OneDrive environment ...

- they all have a single Worksheet ... but the Worksheet name is unique to each file

- the data is in a range ie NOT an Excel Table, with column headers in row 1

- they ONLY share one common column "ID" ... ALL other columns headers are unique to each file

- in a new Workbook, I want to create PQ queries for each of the 78 files with NO transformation ie the data as is in its own table on its own Worksheet ... but this must be against the https:// because the queries will not always be run by me/on my own machine

I could do this manually, but it's going to be a pretty laborious task, in particular getting the correct https:// path (including deleting the trailing ?web=1 that MS insists on adding!) for each of the 78 files, manually creating 78 queries ...

Is there any way I can write some eg VBA that loops through a list of the 78 files and does this for me?

Thanks for any help anyone can give me ...

[v-priyankata]

Hi @adambc 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-priyankata]

Hi @adambc 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[slorin · Accepted solution]

Bonjour @adambc 

Je réponds en français, c'est plus simple pour moi

C'est possible avec une requête Power Query chargée dans le modèle de données (Power Pivot) et une petite macro VBA pour modifier chaque table en DAX

La requête Power Query doit regrouper vos 78 fichiers et la macro va filtrer sur le nom de chaque fichier

A adapter bien évidemment

code de la macro

Sub Duplique()
For Each Nom In Range("Tableau1[Column1]")
  Sheets.Add After:=ActiveSheet
  ActiveSheet.Name = Nom

  Set Data = ActiveSheet.ListObjects.Add(SourceType:=4, Source:=ActiveWorkbook.Connections("Requête - Tableau1"), Destination:=Range("$A$1"))
  Data.TableObject.WorkbookConnection.OLEDBConnection.CommandText = Array("EVALUATE FILTER(Tableau1, [Column1]=""" & Nom & """)")
  Data.TableObject.WorkbookConnection.OLEDBConnection.CommandType = xlCmdDAX
  Data.Refresh
Next Nom
End Sub

Voir la vidéo en pièce jointe

Stéphane

[v-priyankata]

Hi @adambc 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[adambc]

Only have 365 Business and therefore don't have the SP Folder connector!

Not sure I understand the PQ function well enough - any pointers to resources I could study?

And I don't want to combine - I want 78 Worksheets with 78 Tables!

Thanks anyway ...

[lbendlin]

Are you sure you want 78 queries?

Consider using VBA (as you mentioned) to construct the PBIR format or the raw .pq format

[adambc]

I want 78 WSs with 78 Tables - if that can be done in a single PQ great?

I DO NOT want to Combine the 78 files - the VBA I then need becomes somewhat more complex!

[lbendlin]

No, this cannot be "done in a single PQ".

[lbendlin]

Use the SharePoint Folder connector to enumerate the files. Then create a Power Query function that handles all the sheet navigation. Lastly, combine the results.