cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Microsoft
Microsoft

Expand xml file that column has text and table types

Hi,

 

I import an XML file and all columns can be expanded in the UI. However, one of the columns contains both text and table types that I can't see a data drill button. I've tried any methods I can find but it didn't work.

 

Book.png

 

Are there any other ways to solve this?

 

Thanks

8 REPLIES 8
Highlighted
Community Champion
Community Champion

@z22741 

If you filter only table and try, it should work. Since the column has mixed data types, the expand option isn't active.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Highlighted

@Fowmy 

Thanks for getting me back. I actually need the data inside the table. Do you happen to know how to do that?

Highlighted
Community Champion
Community Champion

@z22741 

I recreated your scenario and managed to expand the table after removing non-table rows. check the steps after Converted to the Table in the attached file

You can download the file: HERE

Fowmy_0-1600505694550.png

Fowmy_1-1600505710477.png

 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Highlighted

Sorry I didn't have my question clear. I'd love to keep all data together. For example, the end result should have row 3, 5 in figure 1 + figure 2 all the data.

 

If this' the data I have

z22741_2-1600534370975.png

 

and this will be if I clicked the table and what the data looks like. Only a list of data

 

z22741_0-1600534267896.png

 

Highlighted
Community Champion
Community Champion

@z22741 

Not quite clear about what exactly is needed, can you provide an example?

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Highlighted

@Fowmy 

The table I have

IdBook
1Table
2123456
3789101

 

If you click Table, it's a list of Book Id also

Book.Value
abc123
def124
ghj125

 

The end result I expect is

IdBook
1abc123
1def124
1ghj125
2123456
3789101

 

Since I can't find an expand button, I don't know how to get all the data inside of the table

 

 

 

Highlighted
Community Champion
Community Champion

@z22741 

Check the steps in the attached file and adjust as per your table. Your tables should look as per Start step.

You can download the file: HERE


Your initial table was this:

Fowmy_0-1600547838349.png


After Transformation:

Fowmy_1-1600547867454.png

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

 




 

Highlighted

@Fowmy  Thanks for all the helps you offered so far!

 

Don't know why it doesn't work for me. It gave me null for all the rows I expanded.

 

Below is the data have and when I click Table it showed "Element: Text". Not sure if that's why it won't work?
2020-09-19 14_25_56-rbac - Power Query Editor.png

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors