cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
YasminYas Regular Visitor
Regular Visitor

How to do pivoting with grouping

https://www.dropbox.com/s/rki3lhmh6augvqk/Sample%20data.xls?dl=0SampleFile attachedN1.PNGSample data with repetitive values except GroupnameN2.PNGOutput should be Each user should get only one row with all the column till group name 1,Material1, close1, group name 2,Material2, close2,this continues for each userhelp appreciated

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: How to do pivoting with grouping

hi, @YasminYas 

You could try this way:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpKzAKSjkFAwlzf0EjfyMDQHMj2zEvJLMtMKU3MAXL884CES2JmTiWMgyntn5aGpMjdEFkHUCpWh6bWGdHXOmP6WmdCX+tM6WudGX2tM6evdRb0tc6SvtYZGtDZPjqXK4bYChYnINsvMxdIOvl5+kJsNIXZGO7q6u0TCVLt5oZuQz7Y8PLU1GyweRBuPsjaTGRl4AyIMMePPpaaDYSl5gNhqcVAWGqJbmksAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [FirstName = _t, LastName = _t, ScreenName = _t, CreateDate = _t, ActivityUpdates = _t, ActivityUpdatesBannerNotifications = _t, WEEK = _t, UPDATES = _t, NEWARRAIVAL = _t, #"ON/OFF_" = _t, #"ON/OFF" = _t, MPFCommunityDigest = _t, GroupName = _t, ANS1 = _t, Ans2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FirstName", type text}, {"LastName", type text}, {"ScreenName", type text}, {"CreateDate", type date}, {"ActivityUpdates", type text}, {"ActivityUpdatesBannerNotifications", type text}, {"WEEK", type text}, {"UPDATES", type text}, {"NEWARRAIVAL", type text}, {"ON/OFF_", type text}, {"ON/OFF", type text}, {"MPFCommunityDigest", type text}, {"GroupName", type text}, {"ANS1", type text}, {"Ans2", type text}}),
    Partition = Table.Group( #"Changed Type", {"FirstName"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"LastName", "ScreenName", "CreateDate", "ActivityUpdates", "ActivityUpdatesBannerNotifications", "WEEK", "UPDATES", "NEWARRAIVAL", "ON/OFF_", "ON/OFF", "MPFCommunityDigest", "GroupName", "ANS1", "Ans2", "Index"}, {"LastName", "ScreenName", "CreateDate", "ActivityUpdates", "ActivityUpdatesBannerNotifications", "WEEK", "UPDATES", "NEWARRAIVAL", "ON/OFF_", "ON/OFF", "MPFCommunityDigest", "GroupName", "ANS1", "Ans2", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Partition", "Custom", each [GroupName]&"-"&[ANS1]&"-"&[Ans2]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"GroupName", "ANS1", "Ans2"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Index", type text}}, "en-US")[Index]), "Index", "Custom"),
    #"Merged Columns" = Table.CombineColumns(#"Pivoted Column",{"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Merged Columns", "Merged", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3", "Merged.4", "Merged.5", "Merged.6", "Merged.7", "Merged.8", "Merged.9", "Merged.10", "Merged.11", "Merged.12", "Merged.13", "Merged.14", "Merged.15", "Merged.16", "Merged.17", "Merged.18", "Merged.19", "Merged.20", "Merged.21", "Merged.22", "Merged.23", "Merged.24", "Merged.25", "Merged.26", "Merged.27", "Merged.28", "Merged.29", "Merged.30", "Merged.31", "Merged.32", "Merged.33", "Merged.34", "Merged.35", "Merged.36"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", type text}, {"Merged.2", type text}, {"Merged.3", type text}, {"Merged.4", type text}, {"Merged.5", type text}, {"Merged.6", type text}, {"Merged.7", type text}, {"Merged.8", type text}, {"Merged.9", type text}, {"Merged.10", type text}, {"Merged.11", type text}, {"Merged.12", type text}, {"Merged.13", type text}, {"Merged.14", type text}, {"Merged.15", type text}, {"Merged.16", type text}, {"Merged.17", type text}, {"Merged.18", type text}, {"Merged.19", type text}, {"Merged.20", type text}, {"Merged.21", type text}, {"Merged.22", type text}, {"Merged.23", type text}, {"Merged.24", type text}, {"Merged.25", type text}, {"Merged.26", type text}, {"Merged.27", type text}, {"Merged.28", type text}, {"Merged.29", type text}, {"Merged.30", type text}, {"Merged.31", type text}, {"Merged.32", type text}, {"Merged.33", type text}, {"Merged.34", type text}, {"Merged.35", type text}, {"Merged.36", type text}})
in
    #"Changed Type1"

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Community Support Team
Community Support Team

Re: How to do pivoting with grouping

hi, @YasminYas

Try this code in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpKzAKSjkFAwlzf0EjfyMDQHMj2zEvJLMtMKU3MAXL884CES2JmTiWMgyntn5aGpMjdEFkHUCpWh6bWGdHXOmP6WmdCX+tM6WudGX2tM6evdRb0tc6SvtYZGtDZPjqXK4bYChYnINsvMxdIOvl5+kJsNIXZGO7q6u0TCVLt5oZuQz7Y8PLU1GyweRBuPsjaTGRl4AyIMMePPpaaDYSl5gNhqcVAWGqJbmksAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [FirstName = _t, LastName = _t, ScreenName = _t, CreateDate = _t, ActivityUpdates = _t, ActivityUpdatesBannerNotifications = _t, WEEK = _t, UPDATES = _t, NEWARRAIVAL = _t, #"ON/OFF_" = _t, #"ON/OFF" = _t, MPFCommunityDigest = _t, GroupName = _t, ANS1 = _t, Ans2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FirstName", type text}, {"LastName", type text}, {"ScreenName", type text}, {"CreateDate", type date}, {"ActivityUpdates", type text}, {"ActivityUpdatesBannerNotifications", type text}, {"WEEK", type text}, {"UPDATES", type text}, {"NEWARRAIVAL", type text}, {"ON/OFF_", type text}, {"ON/OFF", type text}, {"MPFCommunityDigest", type text}, {"GroupName", type text}, {"ANS1", type text}, {"Ans2", type text}}),
    Partition = Table.Group( #"Changed Type", {"FirstName"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"LastName", "ScreenName", "CreateDate", "ActivityUpdates", "ActivityUpdatesBannerNotifications", "WEEK", "UPDATES", "NEWARRAIVAL", "ON/OFF_", "ON/OFF", "MPFCommunityDigest", "GroupName", "ANS1", "Ans2", "Index"}, {"LastName", "ScreenName", "CreateDate", "ActivityUpdates", "ActivityUpdatesBannerNotifications", "WEEK", "UPDATES", "NEWARRAIVAL", "ON/OFF_", "ON/OFF", "MPFCommunityDigest", "GroupName", "ANS1", "Ans2", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Partition", "Custom", each [GroupName]&"-"&[ANS1]&"-"&[Ans2]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"GroupName", "ANS1", "Ans2"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Index", type text}}, "en-US")[Index]), "Index", "Custom"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Pivoted Column", "1", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"1.1", "1.2", "1.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"1.1", type text}, {"1.2", type text}, {"1.3", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "2", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"2.1", "2.2", "2.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"2.1", type text}, {"2.2", type text}, {"2.3", type text}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "3", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"3.1", "3.2", "3.3"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"3.1", type text}, {"3.2", type text}, {"3.3", type text}}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Changed Type3", "4", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"4.1", "4.2", "4.3"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"4.1", type text}, {"4.2", type text}, {"4.3", type text}}),
    #"Split Column by Delimiter4" = Table.SplitColumn(#"Changed Type4", "5", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"5.1", "5.2", "5.3"}),
    #"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter4",{{"5.1", type text}, {"5.2", type text}, {"5.3", type text}}),
    #"Split Column by Delimiter5" = Table.SplitColumn(#"Changed Type5", "6", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"6.1", "6.2", "6.3"}),
    #"Changed Type6" = Table.TransformColumnTypes(#"Split Column by Delimiter5",{{"6.1", type text}, {"6.2", type text}, {"6.3", type text}}),
    #"Split Column by Delimiter6" = Table.SplitColumn(#"Changed Type6", "7", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"7.1", "7.2", "7.3"}),
    #"Changed Type7" = Table.TransformColumnTypes(#"Split Column by Delimiter6",{{"7.1", type text}, {"7.2", type text}, {"7.3", type text}}),
    #"Split Column by Delimiter7" = Table.SplitColumn(#"Changed Type7", "8", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"8.1", "8.2", "8.3"}),
    #"Changed Type8" = Table.TransformColumnTypes(#"Split Column by Delimiter7",{{"8.1", type text}, {"8.2", type text}, {"8.3", type text}}),
    #"Split Column by Delimiter8" = Table.SplitColumn(#"Changed Type8", "9", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"9.1", "9.2", "9.3"}),
    #"Changed Type9" = Table.TransformColumnTypes(#"Split Column by Delimiter8",{{"9.1", type text}, {"9.2", type text}, {"9.3", type text}}),
    #"Split Column by Delimiter9" = Table.SplitColumn(#"Changed Type9", "10", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"10.1", "10.2", "10.3"}),
    #"Changed Type10" = Table.TransformColumnTypes(#"Split Column by Delimiter9",{{"10.1", type text}, {"10.2", type text}, {"10.3", type text}}),
    #"Split Column by Delimiter10" = Table.SplitColumn(#"Changed Type10", "11", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"11.1", "11.2", "11.3"}),
    #"Changed Type11" = Table.TransformColumnTypes(#"Split Column by Delimiter10",{{"11.1", type text}, {"11.2", type text}, {"11.3", type text}}),
    #"Split Column by Delimiter11" = Table.SplitColumn(#"Changed Type11", "12", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"12.1", "12.2", "12.3"}),
    #"Changed Type12" = Table.TransformColumnTypes(#"Split Column by Delimiter11",{{"12.1", type text}, {"12.2", type text}, {"12.3", type text}})
in
    #"Changed Type12"

Here is pbix file, please try it.

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
YasminYas Regular Visitor
Regular Visitor

Re: How to do pivoting with grouping

Hi Lin,
Thanks for your response.

I was kind of achieve the result. But I have more than 100 groups and groups get adding always.
So every time i can split the rows and rename them for 100 of groups every time.
if you can suggest any better way to achieve this dynamically.

 

 

Community Support Team
Community Support Team

Re: How to do pivoting with grouping

hi, @YasminYas 

You could try this way:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpKzAKSjkFAwlzf0EjfyMDQHMj2zEvJLMtMKU3MAXL884CES2JmTiWMgyntn5aGpMjdEFkHUCpWh6bWGdHXOmP6WmdCX+tM6WudGX2tM6evdRb0tc6SvtYZGtDZPjqXK4bYChYnINsvMxdIOvl5+kJsNIXZGO7q6u0TCVLt5oZuQz7Y8PLU1GyweRBuPsjaTGRl4AyIMMePPpaaDYSl5gNhqcVAWGqJbmksAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [FirstName = _t, LastName = _t, ScreenName = _t, CreateDate = _t, ActivityUpdates = _t, ActivityUpdatesBannerNotifications = _t, WEEK = _t, UPDATES = _t, NEWARRAIVAL = _t, #"ON/OFF_" = _t, #"ON/OFF" = _t, MPFCommunityDigest = _t, GroupName = _t, ANS1 = _t, Ans2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FirstName", type text}, {"LastName", type text}, {"ScreenName", type text}, {"CreateDate", type date}, {"ActivityUpdates", type text}, {"ActivityUpdatesBannerNotifications", type text}, {"WEEK", type text}, {"UPDATES", type text}, {"NEWARRAIVAL", type text}, {"ON/OFF_", type text}, {"ON/OFF", type text}, {"MPFCommunityDigest", type text}, {"GroupName", type text}, {"ANS1", type text}, {"Ans2", type text}}),
    Partition = Table.Group( #"Changed Type", {"FirstName"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"LastName", "ScreenName", "CreateDate", "ActivityUpdates", "ActivityUpdatesBannerNotifications", "WEEK", "UPDATES", "NEWARRAIVAL", "ON/OFF_", "ON/OFF", "MPFCommunityDigest", "GroupName", "ANS1", "Ans2", "Index"}, {"LastName", "ScreenName", "CreateDate", "ActivityUpdates", "ActivityUpdatesBannerNotifications", "WEEK", "UPDATES", "NEWARRAIVAL", "ON/OFF_", "ON/OFF", "MPFCommunityDigest", "GroupName", "ANS1", "Ans2", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Partition", "Custom", each [GroupName]&"-"&[ANS1]&"-"&[Ans2]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"GroupName", "ANS1", "Ans2"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Index", type text}}, "en-US")[Index]), "Index", "Custom"),
    #"Merged Columns" = Table.CombineColumns(#"Pivoted Column",{"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Merged Columns", "Merged", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3", "Merged.4", "Merged.5", "Merged.6", "Merged.7", "Merged.8", "Merged.9", "Merged.10", "Merged.11", "Merged.12", "Merged.13", "Merged.14", "Merged.15", "Merged.16", "Merged.17", "Merged.18", "Merged.19", "Merged.20", "Merged.21", "Merged.22", "Merged.23", "Merged.24", "Merged.25", "Merged.26", "Merged.27", "Merged.28", "Merged.29", "Merged.30", "Merged.31", "Merged.32", "Merged.33", "Merged.34", "Merged.35", "Merged.36"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", type text}, {"Merged.2", type text}, {"Merged.3", type text}, {"Merged.4", type text}, {"Merged.5", type text}, {"Merged.6", type text}, {"Merged.7", type text}, {"Merged.8", type text}, {"Merged.9", type text}, {"Merged.10", type text}, {"Merged.11", type text}, {"Merged.12", type text}, {"Merged.13", type text}, {"Merged.14", type text}, {"Merged.15", type text}, {"Merged.16", type text}, {"Merged.17", type text}, {"Merged.18", type text}, {"Merged.19", type text}, {"Merged.20", type text}, {"Merged.21", type text}, {"Merged.22", type text}, {"Merged.23", type text}, {"Merged.24", type text}, {"Merged.25", type text}, {"Merged.26", type text}, {"Merged.27", type text}, {"Merged.28", type text}, {"Merged.29", type text}, {"Merged.30", type text}, {"Merged.31", type text}, {"Merged.32", type text}, {"Merged.33", type text}, {"Merged.34", type text}, {"Merged.35", type text}, {"Merged.36", type text}})
in
    #"Changed Type1"

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.