Step 7: Now the data is ready and you can see it in the window. You can also uncheck the checkbox if you do not need that particular column. There appears a list of columns, you can select the columns which need to be in the excel by clicking on the checkbox. Step 6: Again click on the two arrows pointing away from each other options. You will get two options, click on Expand to New Rows. Step 5: You must now see the table in the window, but you may wonder why there is no data on the appeared screen, so just click on the option which has two arrows pointing away from each other to expand the cells.
Step 4: Now a Power Query Editor window will open where we can perform some operations like filter, move, sort, etc. Click on the Into Table option present in the left corner of the window. Step 3: There comes a window where you need to browse and navigate to the location where the JSON file is present. This means you want to import the data from the JSON file into excel. Click on From File and again in the list appeared click on From JSON option. Source = Excel.Step 2: In the data tab click on the Get Data option which has a dropdown and there appears a list of options. Here keep only this column and expand it. After that add custom column to create records from two last columns usingĮach Record.FromList(, How do I go about solving this issue?Īs next step from your second screenshort you may expand last column "rows" to rows. I tried removing duplicates and transpose but those options are leading to the tabular data I am looking for.
The first step was converting the json file to a table, afterwards, I expanded 'value' cell to new rows and did so another time, to get thisimage:įrom there I further expanded and to get all the values. The issue I'm having is figuring out how to to transform the data based on my data I have. I would like the output to look like this: I would like to import the JSON file into Excel, I am using Power Query as I'm not versed in VBA.
0 kommentar(er)
