Forum Discussion
How to turn cells with nested lists into strings Microsoft Excel Powerquery JSON file
Hey there!
I'm a student with no previous experience with Microsoft Excel PowerQuery. I am working with a JSON file. My previous steps with the data have been as follows:
1. Getting the data from the JSON file
2. Navigation to one row that had a nested list of data
3. Converting that row to a single-column table
4. Expanding that single-column table (itself composed of more nested lists)
And here's where I'm stuck: one of those columns has text strings, but also nested lists that include text (I think I am using the term "nested list" right--it is a cell that contains the word List that I can use the "Drill Down" functionality to see as a single-column table under the header "List" with no row names). I would like to convert all of the cells that include nested lists into strings that are separated by spaces where the divisions into rows exist. Can I do this in Microsoft Excel PowerQuery? If so, how? I am totally unfamiliar with the formula functionality.
5 Replies
Perhaps you may provide small sample file? I may only make a guess that Table.FromColumns() could be used, but not sure.
- I would prefer not to share the entire dataset as I feel that may compromise the privacy of my data. What would you recommend to do using Table.FromColumns()?
Entire dataset is not required, just small sample file.
If, for example, you have table with texts in one column and lists in each row for another column, using above function you may create the table where texts from first column will be headers of new table, and all lists - columns. You may continue with transformation of such columns, e.g. expand records if they are inside, etc.
But that's only the guess, not sure how your json is nested.
