Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
Proud to be a Super User!
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"
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |