Forum Discussion
Duplicate rows when expanding JSON list
Hi,
I'm having some problems to expand a list from a JSON document in Power Query.
Look this figure:
The columns dimensions and dimensionsMap have just one value. The column timestamps has 30 values, so, when I expand my table must has 30 rows. Perfect!
The last column values also has 30 values. My expectation: my table should still have only 30 rows. But, instead, my table now has 900 rows.
So, I need this:
| dimensions | dimensionsMap | timestamps | values |
| a | 1 | c | 4 |
| b | 2 | d | 5 |
| c | 3 | e | 6 |
But I have this:
| dimension | dimensionsMap | timestamps | values |
| a | 1 | c | 4 |
| a | 1 | c | 5 |
| a | 1 | c | 6 |
| b | 2 | d | 4 |
| b | 2 | d | 5 |
| b | 2 | d | 6 |
| c | 3 | e | 4 |
| c | 3 | e | 5 |
| c | 3 | e | 6 |
Can you help me?
You can add a custom column with a formula like this to combine those lists into a table. You can then expand that table.
= Table.FromColumns({[dimensions], [dimensionsGroup]})
Pat
2 Replies
You can add a custom column with a formula like this to combine those lists into a table. You can then expand that table.
= Table.FromColumns({[dimensions], [dimensionsGroup]})
Pat
Hi Anonymous,
In fact, this result works as expected as expand feature designs. (Raw table records will be multiple if you expand one field record)
In my opinion, I'd like to suggest you add an index field to your table and do unpivot column on the field which you wanted to expand. Then your field will be converted to 'attribute' and 'value' and you can expand them at the same time without duplicate these field values.After these steps, you will get the unpivoted and expand fields value that mapping with index field values. You can do some mapping and pivot operations to transfer them to raw table structures.
Regards,Xiaoxin Sheng