Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
BornSlippy99
Regular Visitor

Merge columns to create new entries

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:

BornSlippy99_1-1679581660045.png

 

 

To this:

BornSlippy99_2-1679581945267.png

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

 

Thanks!

1 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")

 

jennratten_0-1679585999961.png

 

View solution in original post

4 REPLIES 4
jennratten
Super User
Super User

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"

jennratten_0-1679582964327.png

 

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

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")

 

jennratten_0-1679585999961.png

 

Brilliant, this worked.

 

Thanks a lot!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors