Get Data from folder for XML

[Anonymous]

Hi,

Need help as I got issue with Get Data from Folder feature. The file I'm using are XML. I'm not really understand how XML works, but if I open it in Notepad, there is 1st tag (before Items tag) which is a Date, like this :

So I've done this step :

1. From Power BI Desktop, Get Data, and choose Folder

2. Type the path, and choose Transform Data.

3. In Power Query editor, as the folder contains many other files, I filtered the name to files start with 'xyz' which are my XML files. 

On 1st try, I removed other columns except Content so it become like this :

And press that Combine Files icon on the top right. 

The result is a table of the XML contain but it seems only took data start from Items Tag. 1st question is how to include the 1st tag right before Items tag ?

On 2nd try, I realized before I remove other columns, there is Date Create column, and think I can use that. So leave like this :

And after click Combine Files, the end result still not included that "Date created" column. So, probably my 2nd question is if I want to include that column, which is originally outside the Content, can I and how ? But the 1st questions shoud be fine already.

Thanks in advance.

Wong

[MFelix]

Hi @Anonymous ,

The get data from folder, has two different options.

• Load
  • Returns the values has you have in a simple table with data
• Combine & Transform
  • This option will ask you to select the file to use as model and creates a function were all the informatin is setup accordingly to your needs
    • Opens the XML table
    • Allow to add change columns

Further more on the combine and load you can see there is are several steps that allow to filter the columns before the files being combine.

[Anonymous]

Hi MFelix,

Thank you very much for your insight. 😅

[v-kelly-msft]

Hi @Anonymous ,

Is your issue solved now?If so,could you pls mark the reply as answered to close it?

Much appreciated.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[VoltesDev]

Hi Kelly,

No, it is not.  I've probably not make my self clear in the message. but I don;t know where because I thought it is explain clearly.😅

Thanks.

[MFelix]

Hi @VoltesDev ,

Did you tried to get the information based on the setup I have send out? 

Has refered when  you make the combine there a  couple of columns that are removed one of them is the date created and you can keep it in the list if you go to change the step where the column is removed.

Are you abble to send out a  sample of your files.

[VoltesDev]

Hi MFelix,

I found the problem, it looks like I missed one step after the 1st "Combine files"

After my screenshot :

There is another grid like this :

And I just click the Table link all this time, whilst not realized that it is actually resembled the XML Tag in my own file.

Just now I tried to cllick the small icon on the Items column header (is it heavy lifting icon ??)

and choose the "Use original column name as prefix" (need to do it twice, I supposed to make field refreshed), then it will show me all columns including the source name and my 1st XML tag before Items.

Aplogize, I should realized it sooner.

Many thanks for your help and patience.

regards,

[MFelix]

Glad you could figure it out.

As I refered instead of the combine if you choose the combine & load that would simplify your life. Try to check it out.

Please mark your answer as the correct one so it can help others.

[VoltesDev · Accepted solution]

Hi MFelix,

I'm afraid not, if using Combine & Load, then after it either Load or again choose Transform data, it will directly read my XML and give me the result without epand the 'table' inside the Item tag.

Like this:

So I think the best way still need to choose Transform at the very first. And this step also being tearched in every Power BI courses, they all saying to"not" use that, instead go to "Transform"

Thanks