cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JoaoMS
Helper II
Helper II

Unpivot table with many fields

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:

Pic1.png

   W1W1W2W2
 ProjectPriorityDay ShiftNight ShiftDay ShiftNight Shift
Supplier 1A14040
  22323
Supplier 2A15252
 B13434
  22020
  33232

 

And this how it should be transformed in Power Query:

Pic 2.png

Thanks in advanced.

 

Joao

1 ACCEPTED SOLUTION
FarhanAhmed
Super User
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"

 

 

 

 

FarhanAhmed_0-1611230353821.png

 







Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Proud to be a Super User!




View solution in original post

1 REPLY 1
FarhanAhmed
Super User
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"

 

 

 

 

FarhanAhmed_0-1611230353821.png

 







Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!