cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
iperezal Frequent Visitor
Frequent Visitor

Expand colum with lists

Hello,

 

I'm using PowerBI Desktop to display info from a Json file. Table created with imported data contains columns that are lists of objects. When I expand this tables with the Query Editor, this lists generate new duplicate rows where the only value wich is differen is rigth from this column. I have some dubtes about this:

 

- Is the info distorded because of expand lists?

- If, for example, I create a new colum in this dataset and then i whant to representate or compute a mesure, this info is distorded too?

- If affirmative, how can I do to correct this problem?

 

Thanks, regards

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator Eric_Zhang
Moderator

Re: Expand colum with lists

@iperezal

 

Sorry for the delay.


In JSON, an object("[{},{}]" part in your json file) is defined as: An object is an unordered set of name/value pairs. See JSON.org.

So the duplication in your case is by Cartesian product of the different objects. To get a proper data model,  one reasonable way is to duplicate the JSON query and break it into 3 tables.Capture.PNG

 

Then set one:many relationship, you report would show ok.

 

 

9 REPLIES 9
Super User
Super User

Re: Expand colum with lists

My guess is that when you expand the list it is trying to display the entire list and so it duplicates all of the other columns so that each list item is in its own row. What you probably want to do is expand the list and then combine them all back so that the list column is something like:

 

item1,item2,item3

 

correct?

 


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

Proud to be a Datanaut!


iperezal Frequent Visitor
Frequent Visitor

Re: Expand colum with lists

yes, correct

 

It's rigth this

Moderator Eric_Zhang
Moderator

Re: Expand colum with lists

@iperezal

 

I don't quite get your point. Say I have a json file as

 

Capture.PNG

 

And after "Transform->To Table" and "Expand column", I got the data as below.

 

Capture.PNG

 

The above table looks good for me, do you have any concern about this? If you do, could you show your problem in some snapshots?

iperezal Frequent Visitor
Frequent Visitor

Re: Expand colum with lists

yes, that table works for me too. The problem is when, for example body is an Json Array (Objects, strings... Any type)

Moderator Eric_Zhang
Moderator

Re: Expand colum with lists

@iperezal

 

Can you show some JSON sample in your case?

iperezal Frequent Visitor
Frequent Visitor

Re: Expand colum with lists

@Eric_Zhang This is an example to explain my dubtes:

json.pngtable.pngexpanded_table.pngexpanded_table2.pngexpanded_table3.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

You can see: In the final table there are duplicated values from Json file

iperezal Frequent Visitor
Frequent Visitor

Re: Expand colum with lists

@Eric_Zhang

Look this graphic. My info is distorded.

"primero" value for "id" : 10230 should be 4+2 = 6 but powerBI has duplicated my info and I get 12

graphic.png

iperezal Frequent Visitor
Frequent Visitor

Re: Expand colum with lists

any suggestion?

Moderator Eric_Zhang
Moderator

Re: Expand colum with lists

@iperezal

 

Sorry for the delay.


In JSON, an object("[{},{}]" part in your json file) is defined as: An object is an unordered set of name/value pairs. See JSON.org.

So the duplication in your case is by Cartesian product of the different objects. To get a proper data model,  one reasonable way is to duplicate the JSON query and break it into 3 tables.Capture.PNG

 

Then set one:many relationship, you report would show ok.