Skip to main content

Hi everyone!

Anyone knows how to extract data from a database that looks like this?

When I use Inspections, since everything has a one-to-one relationship with an audit_id number, I can easily create visuals from the different columns.

But when I try to use inspection_items, since there is a one-to-many relationship to the different “labels” and their corresponding “responses”, I am stuck and don’t know how to proceed.

 

On the first image below, I put the data in Excel as it helps me visualize it better. The left side shows how the data is downloaded from a website.

On the right is how I visualize it in my head so I can then create a visual in Power BI.

 

At this moment, I only know how to extract one single response per audit_id.

See second image showing what I have in Power BI.

The way I do it is that I choose the label field as part of the visualization, and then I add a filter that chooses only one label and display its corresponding response.

But if I do not know how to add additional labels.

 

I wish there was a way to transform the data in Power Query so I could see the different label options on the Data column in Power BI so I could choose what to display on a visual.

 

Please help. Thank you.

 

 

Hi Jose, someone may be able to provide advice on using Power BI for this, but I would also suggest trying the Excel export from the Inspections Page in the app. It will export up to 10,000 inspections to a flat format much like you have shown in your desired output above and might save you some work.
https://help.safetyculture.com/en-US/000072/

Thank you Austin.

Someone else pointed me towards the use of Pivoting a column in PowerQuery, and after a couple of failed attempts, it works now!

I won’t need to do the bulk export, which will make it even simpler for us to do some more data analytics.

Thank you very much.

Terms & ConditionsCookie settings