Forum Discussion

iperezal's avatar
iperezal
Frequent Visitor
10 years ago
Solved

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, th...
Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    10 years ago

    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.

     

     

iperezal's avatar
iperezal
Frequent Visitor
10 years ago

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's avatar
iperezal
Frequent Visitor
10 years ago

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