Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
z22741
Employee
Employee

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
Fowmy
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@Fowmy 

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

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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

 

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@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

 

 

 

@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

 




 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.