Best approach to Unpivot three columns into a child table?

[OneWithQuestion]

I have a SPO list that has three columns.

I want to convert those columns into rows on a child table.

I tried UnPivot and selecting just those three columns, but that runs for about 10 minutes (only 400 rows on the list) and then shows errors for every field.  I assume I'm missing something there.

The columns are for logged failures.  A process can fail up to three times before it is terminated.

Failure1, Failure2, Failure3 are the columns.

ProcessRunID, other columns, Failure1, Failure2, Failure3

45,xxx,Input Error, Safety Fault, User Abort

etc...there are perhaps 15 failure options that could be in any of the three columns.  (they have a lookup list)

I want to instead make a child table that has

Failure Events

ProcessRunID, Failure

45, InputError

45,SafetyFault,

45, User Abort

..more rows of course for each process ID, process could have zero to 3 failures.

I can't quite work out how to create that child table and link it back to the main SPO list in the Power BI desktop editor?

Suggestions?

Thanks!

The reason I want this is so that I can just graph most common failure type over all runs.

Basically a bar chart that will show counts of failures so we can see which ones happen most often and when.

[Ashish_Mathur]

Hi,

Share your link from where i can download your file.  Also show your epxected output there.

[OneWithQuestion · Accepted solution]

After playing with this a bit to reformat so I could post my data I found a work around that works for me at least:

I duplicate the query (of the SPO list) then I remove ALL columns except for the ID and the failure reasons 1, 2, and 3.

Then I can do the unpivot and it works just fine in PBI without throwing any errors.

I think those extra columns were just messing things up 🙂

Thanks!