Extract single table row/record into multi records

[aob1]

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

[AlB · Accepted solution]

@aob1 

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 

[aob1]

Thanks very much to you both, worked perfectly. 🙂

[amitchandak]

@aob1 , Unpivot and then split column by space

unpivot- https://radacad.com/pivot-and-unpivot-with-power-bi
split

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

[AlB]

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 

[aob1]

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,51

Wasn't expecting responses so fast!! Thanks guys, glad I was sniffing towards the right approach