Forum Discussion
Extract single table row/record into multi records
Hi all, hoping for some guidance here as I'm not very strong with DAX.
I have a Smartsheet datasource that we use kind of like a project management tool for forecasting how many hours of work are happening each week for people with different types of skills. The source data table has the format as below :
When I import into PowerBI, I want to convert this into the format below, so I can plot the Week Number (1-52 for the relevant week of the year) as a time-based visualisation on the x-axis with hours on the y-axis :
I've tried doing a transpose but that didn't really help, and i think I need to do some DAX loop functions on the original source table but am not 100% sure where to begin. Any guidance appreciated
Place the following M code in a blank query to see the steps:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkktLjE0MlbSUfJNTc5IzMtMBjLBfEMjpVgdZAUhQAVA+czEPCDHxASkzgJNiWtOanJJUWZyYg5IiQGQsDCFKzExNUO1xhhkhpkZmgIUa4zA1higKUGxBuhOHSVTQ6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project#" = _t, #"Resource Type" = _t, #"Week 1" = _t, #"Week 2" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project#", type text}, {"Resource Type", type text}, {"Week 1", Int64.Type}, {"Week 2", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project#", "Resource Type"}, "Week Number", "Hours forecast") in #"Unpivoted Columns"IT is the last step that's important. You just select the weekNumber columns and choose unpivot columns in the GUI, then change column names if necessary
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
5 Replies
Place the following M code in a blank query to see the steps:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkktLjE0MlbSUfJNTc5IzMtMBjLBfEMjpVgdZAUhQAVA+czEPCDHxASkzgJNiWtOanJJUWZyYg5IiQGQsDCFKzExNUO1xhhkhpkZmgIUa4zA1higKUGxBuhOHSVTQ6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project#" = _t, #"Resource Type" = _t, #"Week 1" = _t, #"Week 2" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project#", type text}, {"Resource Type", type text}, {"Week 1", Int64.Type}, {"Week 2", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project#", "Resource Type"}, "Week Number", "Hours forecast") in #"Unpivoted Columns"IT is the last step that's important. You just select the weekNumber columns and choose unpivot columns in the GUI, then change column names if necessary
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Thanks very much to you both, worked perfectly. 🙂
Hi aob1
You have to use the pivot/unpivot feature. If you share a copy of your initial table in text format, so that it can be copied, I'll show you the steps. A few example columns will suffice; we don't need the 52 cols
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Project#,Resource Type,Week 1,Week 2
Test123,Mechanic,23,12
Test123,Technician,44,28
Test123,Electrical,40,85
Test456,Mechanic,34,66
Test456,Technician,24,20
Test456,Electrical,12,51Wasn't expecting responses so fast!! Thanks guys, glad I was sniffing towards the right approach
aob1 , Unpivot and then split column by space
unpivot- https://radacad.com/pivot-and-unpivot-with-power-bi
splithttps://www.tutorialgateway.org/how-to-split-columns-in-power-bi/
