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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Convert a Json List into a table and add further columns

Hi,

 

I have a Json file that I have unpacked which has given me a table containing some flat values and some colums containing multiple values held as a Json list.

 

I would like to extract the column containing the multiple values into a new table but also take the Id colum of the table to act as a key back to the original table.

 

The table looks a little like this:

Table A

IdCountries
1List
2List

 

And what I'd like to achieve is this:

 

Table B

Table A.IdCountry
1USA
1France
2Great Britain
2Germany
2Italy

 

Any help would be greatly appreciated!

1 REPLY 1
MFelix
Super User
Super User

Hi @Anonymous ,

 

On the top of the query editor you should have a convert to table button, click next on the list values you should have to arrows if you click that you will get the expand values and the necessary result.

 

Follow the image below:

json.gif


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.