Switching from single xml-file and appended files to importing xml-folder

[Anonymous]

Hi, 

Currently in Power Query, I import a xml-file for month 1 Region East and append xml-file for month 1 Region West, file for month 2 Region East, month 2 Region West and so on:

Instead of appending new files each month I want to import the entire folder with xml-files so that my report automtically import new xml-files whenever there are placed in the folder. 

How can I do this without breaking my applied steps? 

The beginning of my query looks like this: 

[Jimmy801]

Hello @Anonymous 

were you able to solve the problem with any reply given?

If so, 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

All the best

Jimmy

[v-juanli-msft]

Hi @Anonymous 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please feel free to let me know.

Best Regards

Maggie

[Jimmy801 · Accepted solution]

Hello @Anonymous 

- Create one query that does exactly what you need

- Create a parameter "FileName" and maintain one complete path as value

- Enter in the advanced editor and change the part of the filename like "C:\.....\example.xlm" with your parameter like Folder.Contentes(FileName)

- rightclick on your query and select "create function". Call it "fnGetData"

- add a new query that reads your folder

- apply a filter that only the files that you need are visilbe

- add a new column and enter the following = fnGetData([Folder Path]&[Name])

- Exapand table

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[Anonymous]

Hi @Jimmy801 

Sorry for my late response due to christmas vacation. 

Some questions regarding your suggestion below; 

- By "one query.. exactly what I need" you mean one query with all of the applied steps including where I also append the other xml files? (picture 1) 

- What does "maintain one complete path as value" means? How should I create the parameter? (picture 2)

pic1

pic2

 

[Jimmy801]

Hello @Anonymous ,

no problem..

about your 2 questions:

no, create one query that processes only ONE xml file as you need it, without including any appending

create a parameter with the complete file path for ONE xml file, like that

the other steps as already stated in my solution

Hope this helps

Jimmy

[Anonymous]

Hi @Jimmy801 

Im obviously doing something wrong: 

I have created a more simple query to test. I then create a parameter with the xml-file in my folder (pic3) and tried to change the query in the advanced editor as you wrote (pic 4). 

pic3

pic4

[Jimmy801]

Hello @Anonymous 

just delete the quotation marks that are surrounding the parameter

Jimmy

[Anonymous]

Did so and now it gives me 

[Jimmy801]

Hello @Anonymous 

the query should treat an xml-file, but your query starts from a folder. So probably you have to remove also the function Folder.Contents from your parameter.. that it looks like Xml.Tables(Filename),

Jimmy

[Anonymous]

Thanks! @Jimmy801