Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
YasminYas
Helper I
Helper I

How to do pivoting with grouping

‎02-13-2019 07:09 AM

https://www.dropbox.com/s/rki3lhmh6augvqk/Sample%20data.xls?dl=0SampleFile attachedSample data with repetitive values except GroupnameSample data with repetitive values except GroupnameOutput 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 userOutput 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

Labels:
Message 1 of 4
359 Views
0
1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support
In response to YasminYas

‎02-25-2019 12:27 AM

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.

View solution in original post

Message 4 of 4
317 Views
0
3 REPLIES 3
v-lili6-msft
Community Support
Community Support

‎02-14-2019 12:11 AM

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.
How to do pivoting with grouping.pbix
Message 2 of 4
338 Views
0
YasminYas
Helper I
Helper I
In response to v-lili6-msft

‎02-20-2019 01:51 AM

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.

 

 

Message 3 of 4
327 Views
0
v-lili6-msft
Community Support
Community Support
In response to YasminYas

‎02-25-2019 12:27 AM

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.
Message 4 of 4
318 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All