Data Transformation in PowerBI from JSON format

[rnian18]

Hey guys,  I am pulling some JSON data and am trying to move a column to a row.  Please see the below pictures:

After some initial transformations, I have the following table.  Here, the test1 column corresponds to the Epoch time and test2 column has the data corresponding to that Epoch time.  Thus, there are 5 epoch times and 5 different values.  

The final table I want would be of the following format plus the Epoch time as the first column.  Basically, I transposed the column and split the comma delimited data into their own respective rows.  However, I then want to have another column before the "MONTH" column called Time.  This column would have the time values from the previous column:

In this example, the Epoch times for all the columns are identical.  However, there will also be times where the data is read asynchronously.  Thus, there might be an entry for "MONTH" at that time, but no entry for "CURRENT_MODE".  In such scenarios, I want the data to just be null.

Is this transformation possible in PowerBI?

[Anonymous · Accepted solution]

Hi @rnian18 ,

You may check the pivot and unpivot feature in Query Editor.

https://docs.microsoft.com/en-us/power-query/pivot-columns 

https://docs.microsoft.com/en-us/power-query/unpivot-column 

Best Regards,

Jay

[rnian18]

Hi Jay,

Yup I figured something out with the pivot columns on Wednesday.