Jun 16 2021 02:42 PM - edited Jun 16 2021 10:36 PM
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.
Jun 16 2021 02:59 PM
Perhaps you may provide small sample file? I may only make a guess that Table.FromColumns() could be used, but not sure.
Jun 16 2021 03:08 PM
Jun 16 2021 03:29 PM
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.
Jun 16 2021 03:45 PM
Jun 16 2021 10:36 PM
@Struggling__StudentUPDATE: I figured out a workaround