Forum Discussion
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
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.
Then set one:many relationship, you report would show ok.
9 Replies
- Greg_Deckler
Community Champion
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?
yes, correct
It's rigth this
Eric_ZhangMicrosoft Employee
I don't quite get your point. Say I have a json file as
And after "Transform->To Table" and "Expand column", I got the data as below.
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?
any suggestion?
- Eric_Zhang
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.
Then set one:many relationship, you report would show ok.
