Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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.
any suggestion?
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.
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
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?
yes, that table works for me too. The problem is when, for example body is an Json Array (Objects, strings... Any type)
@Eric_Zhang This is an example to explain my dubtes:
You can see: In the final table there are duplicated values from Json file
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