Hello,
I'm struggling with what I feel should be something simple(!).
I've been given a list of hosts for a dinner; each host has a (different) number of guests and each diner has a choice of menu items - see attachment, blue table. The table is in the form of one diner per row.
I want to create a query that will give a mail-merge source file to allow me to send a single email to each host containing the details of all diners in their group (ie their guests and themselves). For this I think I need a table with one record per host and separate columns for each diner and their choice of meal - see green table in attachment.
I feel that this should have something to do with "pivot without aggregation", but an presently stuck, and would appreciate some help, please!
Many thanks.
Pieter
Hi Pieter,
Please try uploading your file again. Be sure to click the 'Start Upload' button after selecting your file and wait for the grey check mark beside the file size to indicate when it's completed.
Mynda
Hi Mynda,
Blast! Here it is again.
As ever,
Pieter
I've made a bit of progress by looking at Catalin's response to "how to transpose a particular column values that tie to userid into Cross table (pivot) in Power Query" a couple of years ago.
I've got a long way by merging the name and meal fields and then using "each Table.Transpose(Table.SelectColumns..." and this gets me a lomg way towards the solution I want (see attached). The only thing I'd still like to do is to keep "Name" and "Meal" separate. I'd appreciate any suggestions.
As ever,
Pieter
I've achieved my desired result by using:
let
Source = Excel.CurrentWorkbook(){[Name="tblDiners"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Host", type text}, {"Name", type text}, {"Meal", type text}}),
#"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Name", "Meal"},Combiner.CombineTextByDelimiter("#", QuoteStyle.None),"Merged"),
#"Grouped Rows" = Table.Group(#"Merged Columns", {"Host"}, {{"Count", each _, type table [Host=nullable text, Merged=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Transpose(Table.SelectColumns([Count],{"Merged"}))),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Column1", Splitter.SplitTextByDelimiter("#", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column2", Splitter.SplitTextByDelimiter("#", QuoteStyle.Csv), {"Column2.1", "Column2.2"}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1", "Column3", Splitter.SplitTextByDelimiter("#", QuoteStyle.Csv), {"Column3.1", "Column3.2"}),
#"Split Column by Delimiter3" = Table.SplitColumn(#"Split Column by Delimiter2", "Column4", Splitter.SplitTextByDelimiter("#", QuoteStyle.Csv), {"Column4.1", "Column4.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter3",{{"Column1.1", "Name01"}, {"Column1.2", "Meal01"}, {"Column2.1", "Name02"}, {"Column2.2", "Meal02"}, {"Column3.1", "Name03"}, {"Column3.2", "Meal03"}, {"Column4.1", "Name04"}, {"Column4.2", "Meal04"}})
in
#"Renamed Columns"
... but I'm hoping that there's a neater solution than merging all the name/meal columns and then splitting each result column separately.
Hi Pieter,
You don't need to split each column separately. Simply merge the columns using the same delimiter, then split them in one step. See file attached.
Mynda
Hi Mynda,
Of course - thanks. I really should have thought of that!
I initially merged the Name/meal columns as I couldn't get table.transpose to work with multiple columns. Just wondering if the merge - process - split approach is in fact the best way of achieving the result, or if there's a simpler way.
Really appreciate your help.
As ever,
Pieter
Hi Pieter,
Personally, unless it's causing performance issues, I'd use this approach. What you're wanting to do isn't an ideal tabular layout, so there's no standard/straight forward way to achieve it.
Mynda
