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.
https://www.dropbox.com/s/rki3lhmh6augvqk/Sample%20data.xls?dl=0SampleFile attachedhelp appreciated
Solved! Go to Solution.
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
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
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.
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
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 |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |