Data - JSON - Lists - Records

[DerekTrujillo]

I have an issue that seems really similar to others, but I can't seem to get to the results I want.

I am trying to create a Power BI report based on data in a Sharepoint List.  The data is timesheet information: Employee, approver, time period, number of hours...etc. 

I am able to pull in the data, but it appears the details are in a JSON field - I was able to parse the JSON, but the result is now a 'List' and my options are to Expand to New Rows or Extract Values:Image 1

If I expand to New Rows – I now have two rows with duplicated data. Except the TimeSheetJSON field now has a type of ‘Record’ and my options there are Expand to New Rows or Extract Values:The values here are: 1 – 7 (These are the day number of the week that contain the hours for each day). Ideally these would be dates (I can derive this as there is a week-starting date) The remaining 7 fields are information about the hours – which projects and category they are associated with and if they are billable hours…etc.) I would need all of these fields available. Image 2

What I need to get to is a point where I can retrieve all the data in a meaningful way Seems parent-child / header-detail method would work – if there were a ‘key’ on the detailed record/list.  Something like this (?):
Header:
Record ID | Employee Name | Supervisor Name | Timesheet Status | Total Hours | …
Detail:
Parent Record ID | Work Date | Project | Hours | …

I can SEE the data I need, but want to get this data into a format where I can report on it and to ensure this is repeatable for other timesheets that come in.

[v-yulgu-msft]

Hi @DerekTrujillo ,

Choose that list column, click "To table" butter under transform tab.

Best regards,

Yuliana Gu

[DerekTrujillo]

I think the problem is that there are more than one record in the list and even if I save them as a table - I loose any key attributes to join back to the original data.  This also needs to be on-going / repeatable for other data coming in.  Sometimes there will be 2 records in the list, sometimes 5, sometimes 3...etc. 

[Ricardofaster]

Hi,

Any updates on this? Could you deal with this problem?

Thanks,

Ricardo