cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wahib_mouhoubi
Helper I
Helper I

Problem reading an xml file

Hi,

 

 

i have an XML file as datasource structured as following :

 

<alerts>

                <alert id="1">

                               <market>AA</market>

                               <participant>

                                               <house>X</house>

                                               <account>A-1</account>

                               </participant>

                </alert>

                <alert id="2">

                               <market>AA</market>

                               <participant>

                                               <house>Y</house>

                                               <account>A-1</account>

                                               <account>A-2</account>

                               </participant>

                </alert>

                <alert id="3">

                               <market>AA</market>

                               <participant>

                                               <house>X</house>

                                               <account>B-1</account>

                               </participant>

                </alert>

</alerts>

The result when i load it in power BI query editor is here 

The problem is that when i expand all the columns in the query editor, the account colum has simple records (for the first and third alert) and and table record for the second, i want to create a costum column that takes the first caracter of the value in the account column, for the first and third record i've managed to do it easily but when it comes to the second, as it is a table it's a bit more complicated, ideally what i want to do when i have a table is to cinsider it's first element and the treatment on it but i couldn't find a way to do it.

 

 

 

any help please?

 

thank you.

1 ACCEPTED SOLUTION
PhilipTreacy
Super User III
Super User III

Hi @wahib_mouhoubi 

 

Download sample PBIX file

 

You are trying to get the first value from a table embedded in a column?

So if your embedded table looks like this

tabel.png

you want to extract the value A-1?

To do this, add a Custom Column in your query with this code

try Record.Field([participant.account]{0}, "Element:Text") otherwise [participant.account]

tabel2.png

 

Which gives you this result

tabel3.png

 

Note that this query uses the column/field names you used in your example.  If they are different in your real data, they will need to be changed.

 

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

2 REPLIES 2
PhilipTreacy
Super User III
Super User III

Hi @wahib_mouhoubi 

 

Download sample PBIX file

 

You are trying to get the first value from a table embedded in a column?

So if your embedded table looks like this

tabel.png

you want to extract the value A-1?

To do this, add a Custom Column in your query with this code

try Record.Field([participant.account]{0}, "Element:Text") otherwise [participant.account]

tabel2.png

 

Which gives you this result

tabel3.png

 

Note that this query uses the column/field names you used in your example.  If they are different in your real data, they will need to be changed.

 

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

That's it !

 

Thank you very much !

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors