Expand colum with lists

[iperezal]

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

[iperezal]

any suggestion?

[Eric_Zhang · Accepted solution]

@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.

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

[Greg_Deckler]

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?

[iperezal]

yes, correct

It's rigth this

[Eric_Zhang]

@iperezal

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?

[iperezal]

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

[Eric_Zhang]

@iperezal

Can you show some JSON sample in your case?

[iperezal]

@Eric_Zhang This is an example to explain my dubtes:

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

[iperezal]

@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