Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |