Merge columns to create new entries

[BornSlippy99]

Hi all,

I'm brand new to Power BI so please bear with me. I imagine this is fairly standard but I can't find an article which answers what I'm looking for.

I've created a reporting tool in my work where operators can log data every 2 hours and this is used as a metric to compare performance from day to day. We also assign issues to explain why performance was poor in the 2 hour period. I've been able to make pie charts when there is one issue, but I'd like to add another column where a second issue can be assigned if necessary. I'd like Power BI to treat it as the same data as the first issue, rather showing up in my visuals as issue 1 and issue 2.

Essentially, I'd like to go from this:

To this:

Hope this makes sense. Any help would be very much appreciated.

Thanks!

[jennratten]

Hello - this will produce the expected results.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtY3MgAiI2MlHSULKwMDIAKynIpSE7OL88vzFHQVHIFsBUOgoFKsDqp6QwNUDSkQDc5FiXmpxXBTMLUZwbVhNdUMLh2cUZpXkloEVhULAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Time = _t, #"Issue 1" = _t, #"Issue 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}, {"Issue 1", type text}, {"Issue 2", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date", "Time"}, "Attribute", "Issue")
in
    #"Unpivoted Other Columns"

[BornSlippy99]

Hi Jenn, 

Thanks for the quick response.

Apologies, does that code go into the advanced editor for that query, and does any of the existing code need changed?

Thanks again

[jennratten · Accepted solution]

No worries, you're welcome!  You can click the fx button to the left of the formula bar which will create a new step in the query. Then paste this snippet into the formula bar and replace #"Your Previous Step Name" with the name of your last step.  

#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Your Previous Step Name", {"Date", "Time"}, "Attribute", "Issue")

[BornSlippy99]

Brilliant, this worked.

Thanks a lot!