Unpivot table with many fields

[JoaoMS]

Dear all, I'm looking for a solution to transform the following Excel Table (made manually) into a table in Power BI where I can create some charts and filters in order to have a dynamic report. I know that I need to use the Unpivot option but I don't know exactly how to proceed. This is the basic table regarding the quantity of people involved in different projects with different priorities in each one:

			W1	W1	W2	W2
	Project	Priority	Day Shift	Night Shift	Day Shift	Night Shift
Supplier 1	A	1	4	0	4	0
		2	2	3	2	3
Supplier 2	A	1	5	2	5	2
	B	1	3	4	3	4
		2	2	0	2	0
		3	3	2	3	2

And this how it should be transformed in Power Query:

Thanks in advanced.

Joao

[FarhanAhmed · Accepted solution]

Steps to follow

- Transpose columns in rows

- Merge Shift and Week Columns

- Transpose back rows into columns

- Promote Header

- Replace Empty string with NULL in Project & Supplier

- Fill down in both column

- UnPivot all shift and week column

- Split attribute column to get Shift and week in separate columns

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtKBonBDOGEEIWJ1wNIBRflZqcklYFZmflFmSSWQ6ZJYqRCckZkGEvbLTM8ogfNwyYBMCy4tKMjJTC1SAFnjCMQg2gSIDeA01FIgMoJiYziNYoQRkhGmUCUQGmqEE1TSGGo2hMY03wBOI+SMUewFGxoLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}}),
    #"Transposed Table2" = Table.Transpose(#"Changed Type"),
    #"Merged Columns" = Table.CombineColumns(#"Transposed Table2",{"Column2", "Column1"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Transposed Table3" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table3", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{":", type text}, {"Project:", type text}, {"Priority:", Int64.Type}, {"Day Shift:W1", Int64.Type}, {"Night Shift:W1", Int64.Type}, {"Day Shift:W2", Int64.Type}, {"Night Shift:W2", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","",null,Replacer.ReplaceValue,{"Project:"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Project:"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Filled Down","",null,Replacer.ReplaceValue,{":"}),
    #"Filled Down1" = Table.FillDown(#"Replaced Value1",{":"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filled Down1", {":", "Project:", "Priority:"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}})
in
    #"Changed Type2"