Hi everyone,
Here’s a fun Power Query challenge problem from Chandeep Chhabra of Goodly.
➜ Download Data - https://goodly.co.in/wp-content/uploa…
➜ Post Your Answers Here 
- https://goodly.co.in/create-dynamic-c…
I hope you have fun with the challenge
José Antonio
This is my solution.
let
Source = Excel.Workbook(File.Contents("C:\Goodly\Power Query\Create-Dinamyc-Columns-Power-Query-Output\Create-Dynamic-Columns-Power-Query-Output.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Name", type text}, {"Hobby", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Hobby", Splitter.SplitTextByDelimiter(", ", QuoteStyle.None), {"Hobby.1", "Hobby.2", "Hobby.3"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Name"}, "Attribute", "Value"),
#"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Other Columns",{"Name", "Value"}),
#"Added ListofValues" = Table.AddColumn(#"Removed Other Columns", "ListofValues", each List.Distinct(#"Unpivoted Other Columns"[Value])),
#"Expanded ListofValues" = Table.ExpandListColumn(#"Added ListofValues", "ListofValues"),
#"Added TRUE/FALSE" = Table.AddColumn(#"Expanded ListofValues", "Value.1", each [Value] = [ListofValues], type logical),
#"Pivoted Column" = Table.Pivot(#"Added TRUE/FALSE", List.Distinct(#"Added TRUE/FALSE"[ListofValues]), "ListofValues", "Value.1"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Value"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Name"}, {{"Details", each _, type table [Name=nullable text, Python=number, Monkey Bars=number, Trading=number, Fashion=number, Consutling=number, Sales=number, Social Media=number, Teaching=number, Leadership=number, Management=number, Finance=number, Emp Engagement=number, Sleeping=number, Coding=number, Crossfit=number, YouTube=number, Athletics=number]}}),
#"Aggregated Details" = Table.AggregateTableColumn(#"Grouped Rows", "Details", {{"Python", List.Max, "Python"}, {"Monkey Bars", List.Max, "Monkey Bars"}, {"Trading", List.Max, "Trading"}, {"Fashion", List.Max, "Fashion"}, {"Consutling", List.Max, "Consutling"}, {"Sales", List.Max, "Sales"}, {"Social Media", List.Max, "Social Media"}, {"Teaching", List.Max, "Teaching"}, {"Leadership", List.Max, "Leadership"}, {"Management", List.Max, "Management"}, {"Finance", List.Max, "Finance"}, {"Emp Engagement", List.Max, "Emp Engagement"}, {"Sleeping", List.Max, "Sleeping"}, {"Coding", List.Max, "Coding"}, {"Crossfit", List.Max, "Crossfit"}, {"YouTube", List.Max, "YouTube"}, {"Athletics", List.Max, "Athletics"}}),
#"Changed Type Logical" = Table.TransformColumnTypes(#"Aggregated Details",{{"Python", type logical}, {"Monkey Bars", type logical}, {"Trading", type logical}, {"Fashion", type logical}, {"Consutling", type logical}, {"Sales", type logical}, {"Social Media", type logical}, {"Teaching", type logical}, {"Leadership", type logical}, {"Management", type logical}, {"Finance", type logical}, {"Emp Engagement", type logical}, {"Sleeping", type logical}, {"Coding", type logical}, {"Crossfit", type logical}, {"YouTube", type logical}, {"Athletics", type logical}})
in
#"Changed Type Logical"
Regards,
Fun problem that I found more difficult than expected. It’s pretty straightforward if you take the data “as is”, but making the solution fully generalizable to accept more hobbies and more people was pretty challenging for me. Here’s my solution:
Note: @jafernandezpuga pointed out an error in my original solution. Now revised - thanks Jose!
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“TY5BDoIwEEWv0nTdS2jFhZHECBtDXIx0pI1lhrRl4e0dwBhXTfre//O7Tl/RY9FGX97FMxlVM73wrfaQsr6bTtuTwDaBCzQYtVkr2KWIhAKPkH34fh4W2zLlucQ10EDErahhgii04T5AVDW6AEa1CL0Xc0uDE+GM4DBJ5yRrgGDAEamsQs3jcjAQUI9GVeOkKhr+DeuBHOK0HIryriMsb+tt4pyfoUivJH6zwgtEv/Hczg9p3RUfsYRe+P0D”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Hobby = _t]),
#“Split into Rows” = Table.ExpandListColumn(Table.TransformColumns(Source, {{“Hobby”, Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), “Hobby”),
#“Trim All Columns” = let cols = Table.ColumnNames(#“Split into Rows”) in List.Accumulate(cols, #“Split into Rows”, (state,current) => Table.TransformColumns(state, {current, Text.Trim})),
#“Add TRUE” = Table.AddColumn(#“Trim All Columns”, “Custom”, each true),
#“Pivoted Column” = Table.Pivot(#“Add TRUE”, List.Distinct(#“Add TRUE”[Hobby]), “Hobby”, “Custom”),
#“Add FALSE” = Table.ReplaceValue( #“Pivoted Column”, null, false, Replacer.ReplaceValue, Table.ColumnNames( #“Pivoted Column”)),
#“Bulk Logical” = let TypeCols = List.RemoveFirstN( Table.ColumnNames( #“Add FALSE” ), 1) in List.Accumulate( TypeCols, #“Add FALSE”, (state1, current1) => Table.TransformColumnTypes(state1, {current1, type logical}))
in
#“Bulk Logical”
These challenges are an awesome way to level up your PQ skills and being able to compare your solution with the many other creative ones on the blog is a fantastic learning experience.
Hi everyone,
After speaking with @BrianJ and telling me that the solution had to work when we added more rows of data and other Hobby values to it, I tried my previous solution and realized that by adding new Hobby values, I was not taking them into account.
Here I publish the solution in which it works regardless of the rows and the different values of hobbies.
let
Source = Excel.Workbook(File.Contents("C:\Goodly\Power Query\Create-Dinamyc-Columns-Power-Query-Output\Create-Dynamic-Columns-Power-Query-Output.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Name", type text}, {"Hobby", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Hobby", Splitter.SplitTextByDelimiter(", ", QuoteStyle.None), {"Hobby.1", "Hobby.2", "Hobby.3"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Name"}, "Attribute", "Value"),
#"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Other Columns",{"Name", "Value"}),
ListValues = List.Distinct(#"Unpivoted Other Columns"[Value]),
#"Added ListofValues" = Table.AddColumn(#"Removed Other Columns", "ListofValues", each ListValues),
#"Expanded ListofValues" = Table.ExpandListColumn(#"Added ListofValues", "ListofValues"),
#"Added TRUE/FALSE" = Table.AddColumn(#"Expanded ListofValues", "HasHobbie", each [Value] = [ListofValues] , type logical),
#"Pivoted Column" = Table.Pivot(#"Added TRUE/FALSE", List.Distinct(#"Added TRUE/FALSE"[ListofValues]), "ListofValues", "HasHobbie"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Value"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Name"}, {{"Details", each _, each List.Max(Table.Column(_, "Details")), type logical}}),
#"Aggregated Details" = Table.AggregateTableColumn(#"Grouped Rows", "Details", List.Transform(ListValues, each {_, List.Max, _})),
#"Changed Type Logical" = Table.TransformColumnTypes(#"Aggregated Details",
List.Transform(
List.RemoveFirstN(
Table.ColumnNames(#"Aggregated Details"),
1
),
each {_, Logical.Type}
))
in
#"Changed Type Logical"
Thank you very much Brian.
@jafernandezpuga it is not as difficult as it might seem, these 4 steps suffice. Power Query is amazing:
let
Source = Excel.CurrentWorkbook(){[Name=“Table1”]}[Content],
#“Added Custom” = Table.AddColumn(Source, “Custom”, each “True”),
#“Split Column by Delimiter” = Table.ExpandListColumn(Table.TransformColumns(#“Added Custom”, {{“Hobby”, Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), “Hobby”),
#“Pivoted Column” = Table.Pivot(#“Split Column by Delimiter”, List.Distinct(#“Split Column by Delimiter”[Hobby]), “Hobby”, “Custom”),
#“Replaced Value” = Table.ReplaceValue(#“Pivoted Column”,null,“False”,Replacer.ReplaceValue,Table.ColumnNames(#“Pivoted Column”))
in
#“Replaced Value”
Regards,
Matthias
Super slick. I often overlook the option to split into rows, but it works like a champ here.
Very nicely done! 
Excellent job on your solution, and thanks for highlighting the error in my initial posting - since revised.
I will have to say that @Matthias smoked us both, which is what makes these so much fun and a great learning opportunity.
I’m used to getting smoked by @Melissa on these, so the variety is nice… 